mass changes to cell protection

Is there a way to alter the cell protection based on a condition instead of 
going in and manually changing them?

more exact:  I want to protect all my cells that have a formula, and 
un-protect the ones that do not. 


0
Adam
11/24/2005 3:54:19 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
128 Views

Similar Articles

[PageSpeed] 23

Adam, on way, select all the cells, Ctrl+a, unprotect them all, then edit,
go to, special, check formulas ok, then protect the cells that are selected

-- 
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Adam Kroger @hotmail.com>" <adam_kroger<nospam> wrote in message
news:ftidna3dfNayfxjenZ2dnUVZ_tKdnZ2d@comcast.com...
> Is there a way to alter the cell protection based on a condition instead
of
> going in and manually changing them?
>
> more exact:  I want to protect all my cells that have a formula, and
> un-protect the ones that do not.
>
>


0
11/24/2005 4:12:40 PM
Paul:
I am using Excel97 (probably should have mentioned that) will they still 
work for me?  I did try, but <CTRL>A did not do anything, and I do not know 
where the "Special" feature is.

Adam

"Paul B" <to_much_spam_to_list@nospam.com> wrote in message 
news:ORweRIR8FHA.500@TK2MSFTNGP15.phx.gbl...
> Adam, on way, select all the cells, Ctrl+a, unprotect them all, then edit,
> go to, special, check formulas ok, then protect the cells that are 
> selected
>
> -- 
> Paul B
> Always backup your data before trying something new
> Please post any response to the newsgroups so others can benefit from it
> Feedback on answers is always appreciated!
> Using Excel 2002 & 2003
>
> "Adam Kroger @hotmail.com>" <adam_kroger<nospam> wrote in message
> news:ftidna3dfNayfxjenZ2dnUVZ_tKdnZ2d@comcast.com...
>> Is there a way to alter the cell protection based on a condition instead
> of
>> going in and manually changing them?
>>
>> more exact:  I want to protect all my cells that have a formula, and
>> un-protect the ones that do not.
>>
>>
>
>
> 


0
Adam
11/24/2005 4:45:48 PM
Adam

Hold CTRL key down and hit the "a" key on keyboard.

Alternative.....click on the gray rectangle at top left corner of the row and
column headers.

Either method will select all cells.

Format>Cells>Protection.  Change to "unlocked" by clicking the checkmark out
of the option box.  OK out.

Hit F5>Special>Formulas>OK

All cells with formulas are selected.

Format>Cells>Protection.  Changed to "Locked".  OK out.

Now Tools>Protection>Protect sheet>OK

Give a password or not.


Gord Dibben Excel MVP

On Thu, 24 Nov 2005 10:45:48 -0600, "Adam Kroger"
<adam_kroger<nospam>@hotmail.com> wrote:

>Paul:
>I am using Excel97 (probably should have mentioned that) will they still 
>work for me?  I did try, but <CTRL>A did not do anything, and I do not know 
>where the "Special" feature is.
>
>Adam
>
>"Paul B" <to_much_spam_to_list@nospam.com> wrote in message 
>news:ORweRIR8FHA.500@TK2MSFTNGP15.phx.gbl...
>> Adam, on way, select all the cells, Ctrl+a, unprotect them all, then edit,
>> go to, special, check formulas ok, then protect the cells that are 
>> selected
>>
>> -- 
>> Paul B
>> Always backup your data before trying something new
>> Please post any response to the newsgroups so others can benefit from it
>> Feedback on answers is always appreciated!
>> Using Excel 2002 & 2003
>>
>> "Adam Kroger @hotmail.com>" <adam_kroger<nospam> wrote in message
>> news:ftidna3dfNayfxjenZ2dnUVZ_tKdnZ2d@comcast.com...
>>> Is there a way to alter the cell protection based on a condition instead
>> of
>>> going in and manually changing them?
>>>
>>> more exact:  I want to protect all my cells that have a formula, and
>>> un-protect the ones that do not.
>>>
>>>
>>
>>
>> 
>

0
Gord
11/24/2005 7:30:02 PM
Thanks for the step-by, it cleared up my haze.

Adam Kroger: Excel MoLC < More or Less Confused ;-) >

"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:io4co19hdskeerq0r5sh4eaf6oe0m3oocm@4ax.com...
> Adam
>
> Hold CTRL key down and hit the "a" key on keyboard.
>
> Alternative.....click on the gray rectangle at top left corner of the row 
> and
> column headers.
>
> Either method will select all cells.
>
> Format>Cells>Protection.  Change to "unlocked" by clicking the checkmark 
> out
> of the option box.  OK out.
>
> Hit F5>Special>Formulas>OK
>
> All cells with formulas are selected.
>
> Format>Cells>Protection.  Changed to "Locked".  OK out.
>
> Now Tools>Protection>Protect sheet>OK
>
> Give a password or not.
>
>
> Gord Dibben Excel MVP
>
> On Thu, 24 Nov 2005 10:45:48 -0600, "Adam Kroger"
> <adam_kroger<nospam>@hotmail.com> wrote:
>
>>Paul:
>>I am using Excel97 (probably should have mentioned that) will they still
>>work for me?  I did try, but <CTRL>A did not do anything, and I do not 
>>know
>>where the "Special" feature is.
>>
>>Adam
>>
>>"Paul B" <to_much_spam_to_list@nospam.com> wrote in message
>>news:ORweRIR8FHA.500@TK2MSFTNGP15.phx.gbl...
>>> Adam, on way, select all the cells, Ctrl+a, unprotect them all, then 
>>> edit,
>>> go to, special, check formulas ok, then protect the cells that are
>>> selected
>>>
>>> -- 
>>> Paul B
>>> Always backup your data before trying something new
>>> Please post any response to the newsgroups so others can benefit from it
>>> Feedback on answers is always appreciated!
>>> Using Excel 2002 & 2003
>>>
>>> "Adam Kroger @hotmail.com>" <adam_kroger<nospam> wrote in message
>>> news:ftidna3dfNayfxjenZ2dnUVZ_tKdnZ2d@comcast.com...
>>>> Is there a way to alter the cell protection based on a condition 
>>>> instead
>>> of
>>>> going in and manually changing them?
>>>>
>>>> more exact:  I want to protect all my cells that have a formula, and
>>>> un-protect the ones that do not.
>>>>
>>>>
>>>
>>>
>>>
>>
> 


0
Adam
11/24/2005 8:41:11 PM
Thanks for the feedback Adam

Gord

On Thu, 24 Nov 2005 14:41:11 -0600, "Adam Kroger"
<adam_kroger<nospam>@hotmail.com> wrote:

>Thanks for the step-by, it cleared up my haze.
>
>Adam Kroger: Excel MoLC < More or Less Confused ;-) >
>
>"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
>news:io4co19hdskeerq0r5sh4eaf6oe0m3oocm@4ax.com...
>> Adam
>>
>> Hold CTRL key down and hit the "a" key on keyboard.
>>
>> Alternative.....click on the gray rectangle at top left corner of the row 
>> and
>> column headers.
>>
>> Either method will select all cells.
>>
>> Format>Cells>Protection.  Change to "unlocked" by clicking the checkmark 
>> out
>> of the option box.  OK out.
>>
>> Hit F5>Special>Formulas>OK
>>
>> All cells with formulas are selected.
>>
>> Format>Cells>Protection.  Changed to "Locked".  OK out.
>>
>> Now Tools>Protection>Protect sheet>OK
>>
>> Give a password or not.
>>
>>
>> Gord Dibben Excel MVP
>>
>> On Thu, 24 Nov 2005 10:45:48 -0600, "Adam Kroger"
>> <adam_kroger<nospam>@hotmail.com> wrote:
>>
>>>Paul:
>>>I am using Excel97 (probably should have mentioned that) will they still
>>>work for me?  I did try, but <CTRL>A did not do anything, and I do not 
>>>know
>>>where the "Special" feature is.
>>>
>>>Adam
>>>
>>>"Paul B" <to_much_spam_to_list@nospam.com> wrote in message
>>>news:ORweRIR8FHA.500@TK2MSFTNGP15.phx.gbl...
>>>> Adam, on way, select all the cells, Ctrl+a, unprotect them all, then 
>>>> edit,
>>>> go to, special, check formulas ok, then protect the cells that are
>>>> selected
>>>>
>>>> -- 
>>>> Paul B
>>>> Always backup your data before trying something new
>>>> Please post any response to the newsgroups so others can benefit from it
>>>> Feedback on answers is always appreciated!
>>>> Using Excel 2002 & 2003
>>>>
>>>> "Adam Kroger @hotmail.com>" <adam_kroger<nospam> wrote in message
>>>> news:ftidna3dfNayfxjenZ2dnUVZ_tKdnZ2d@comcast.com...
>>>>> Is there a way to alter the cell protection based on a condition 
>>>>> instead
>>>> of
>>>>> going in and manually changing them?
>>>>>
>>>>> more exact:  I want to protect all my cells that have a formula, and
>>>>> un-protect the ones that do not.
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>> 
>

0
Gord
11/24/2005 9:16:24 PM
Reply:

Similar Artilces:

Change (reverse) scale axis
How can you change the direction of the numbers on the page scale. In other words, the scales are on the left and the top of the page. The left scale starts at zero on the bottom of the page and counts up. The scale on the top of the page starts at zero on the left side of the page and counts to the right. How do you change it so that the scale starts on the right and counts to the left? Thanks. You can't. The best you can do is move the zero point. Pause your mouse over the crosshairs in the ruler at the top-right for a tooltip on how to do this. So you could put the origin ...

How do you keep the data table format from changing
I have tried all sorts of methods to keep this data from changing it's format when it is emailed to another recipient. Help ...

how to?: change which folders are searched in find related context menu
Hello, When you right click on a message and do "find all"->"related messages" or "find all"->"from user", you're basically just starting up an advanced search with some preselected options. One of the preselects is which folders outlook does the search in. How do I change this ? and/or add my own searches? (basically, i'd like to search in inbox and all subfolders) Many Thanks, Alex PS: cross posting....let me know if there's an appropriate group for this sorta question. There is not an option to extend that list. What you c...

cannot load excel file
When trying to load an excel file it will not allow me to open it and shows the message in a separate window " too many different cell formats". I am led to believe (after a bit of investigation) that the file is now too large and needs to be slimmed down. This is fine, but I need to be able to open it first! Is there a way to open this file? Please help. This spreadsheet is important to me. Thank you. A page format is a combination of upper/lower/left/right border, font color, interior color, number format. All the more reason to format large areas all at the same time i...

Column size keeps changing
Using OL2003 In a custom view I have many columns with carefully chosen sizes for each. This remained unchanged as long as I didn't resize them in OL2002. With the new 2003 version, when I click on Help for instance it opens up a sidebar to the right of the screen with the search fields and results and squeezes the view into the left hand pane.. When I close Help, my custom view is "restored" but the columns are all changed. Help! ...

How do I change number formating to start at 0001?
Every time that I try to change the number format I can only start at 01. I must start my numbering at 0001 and shy of manually typing in every number ( I need 2400) I don't know how to do it. Can anyone help? Thanx. Use custom format 0000 type 1,2 etc -- Regards, Peo Sjoblom "Rita and Matt" <Rita and Matt@discussions.microsoft.com> wrote in message news:4F2E186F-07C1-49AA-B52B-24C14136F2DC@microsoft.com... > Every time that I try to change the number format I can only start at 01. > I > must start my numbering at 0001 and shy of manually typing ...

Center of Mass
Hello together, i have a 20*10 matrix with positive integer-values and I have to calculate the center of mass of this "volume". Should be easy in Excel...but what's the easiest way of doing it? Best Regards Gerhard Gerhard, What do the integer values represent, and what does the matrix represent? HTH, Bernie MS Excel MVP "Gerhard" <Gerhard@discussions.microsoft.com> wrote in message news:46E76273-2824-457D-92E9-00AB0211AFAD@microsoft.com... > Hello together, > i have a 20*10 matrix with positive integer-values and I have to calculate > the center ...

Mass Delete
Some how I have created multiple listings for the same item. I think I made any error in importing a csv file. For example I have over 100 separate items with the same SKU (STNP-1) and desription. Is there a way to delete these as a bunch and not each single one. Thanks for any advice. Steve: Each item has a unique ID called the primary key even though you may have entered all of the other information multiple times. Pick one ID, call it X, to keep and delete the others with Tom's statement with the additional qualifier" AND ID <> X". "Steve" wrote: &g...

How to change tick mark labels in z axis from values to texts?
I am drawing a column chart for the dynamic scales in a music piece. Data value in the z axis refers to other customary names (texts). For example, 7 represents ff (fortissimo), 6 represents f (forte), 5 for mf (mezzo forte), etc. I want to show these customary names instead of the values in the labels. Could anybody teach me how to change these tick mark labels in z axis from values to texts? One correction. The labels I want to change is in the Y axis. Thanks. "Flroa" wrote: > I am drawing a column chart for the dynamic scales in a music piece. Data > value in the z...

How would I fill blank cells with the data from a previous cell?
Need to fill blank cells in a column with the data from the previous non-blank cell in Excel 2000. Used to do this in Lotus. Can't seem to get it in Excel try edit>fill>series (select one) Lotus's is simpler. >-----Original Message----- >Need to fill blank cells in a column with the data from the previous >non-blank cell in Excel 2000. Used to do this in Lotus. Can't seem to get it >in Excel > > >. > One way, assuming you mean you have data blank data blank or something like that and you want the cell with data to fill in the blanks below ...

Changing a control on a subform?
I am trying to turn some controls in a subform on or off by using a check box on the main form, can�t get it to work Here is the code on the check box click event Private Sub ckShowAWIPrice_Click() If Me.ckShowAWIPrice = -1 Then Me!frmDataSubform.Form!txtStandardBond.Visible = True Me!frmDataSubform.Form!txtStandardNet.Visible = False End If End Sub i'm guessing that "frmDataSubform" is the name of the form (that you're using as a subform) as it shows in the database window. is it also the name of the *subform control within the main form*? your expression has ...

select cell based on a number in another cell
This is what I want to do I have a cell A1 with the number 40 In another cell I want to use the number 40 to select a cell, something like: =B40+234 Where 40 in B40 is the number typed in A1. how do i do this please? Try this: =INDEX(B:B,A1)+234 -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== <jon.berg@gmail.com> wrote in message news:1133883789.555648.81840@o13g2000cwo.googlegroups.com... This is what I want to do I have a cell A1 with ...

Cannot accept change in appointment
Hello, I cannot accept a change in an appointment. Person 1 creates an appointment and sends in to persons 2 and 3. Then person 2 changes the appointment but person 3 cannot accept this change because outlook says the request is too old. How can i make certain the person 3 can accept this change? We work with outlook 2003 and exchange 2003. Auke Bijlsma Deltaisis ...

Conditional Format based on value in other cell
Hi! i have an issue where i want to conditionally format a cell (red, yellow, or green) based on the value in another cell. For example, in cell B20, the value will be 1 for green, 0 for yellow, -1 for red. I want the value in that cell to change the color of cell B1, which contains a complex formula combining two values from other cells (this is a department expense analysis and that cell contains the variances in $ and in % terms, so its hard to conditional format because of the multiple values in the cell). Can you help? Thanks in advance! Adam Instructions for XL2003 (XL 2007 is simila...

mass mailing causes system to become a bottleneck
Good Day, We running exchange 2003, once a week we send a mass amiler to roughly 1000 recipients with a 1mb attachment. Whenever this happens the exchange server and internet because a bottleneck and end users are not able to do any work for hours at a time. Is there any other method we can use to send these messages? Is there anything I can do on exchange that would prioritize the traffic? Any help would be appreciated. Ray How are you sending this message? Are you using Outlook and BCC? You can defer the sending of the message by selecting Tools > Options > Do not deliver...

Is there an automatic way to change numbers such as 12312001 to da
I have imported a lot of dates from an old notepad file, but they came accross as a numbers ex: December 31, 2001 is 12312001. each cell has a number. I have formated the cells as dates, but will not recognize as a date unless I insert / / in each cell. Is there an automatic way to change the numbers to a date????????? Hi for an addin see: http://www.xldynamic.com/source/xld.QDEDownload.html "LMW" wrote: > I have imported a lot of dates from an old notepad file, but they came > accross as a numbers ex: December 31, 2001 is 12312001. each cell has a > number. I have ...

mass modify
In version 9.0 the GL Mass Modify option does not copy or move the GL account description from the source account. Instead it creates its own description from the account segment descriptions. Is there any way that the mass modify can copy the correct description. This is a big issue for our company because we have more than 50,000 accounts. And it will be impossible for us to update each account description when a new division is created. ...

Adding static character to a cell
I want to insert a $ in column of cells. Use currency as the format, but I want the $ to stay in the cell so it can be used also when printed out. I hope that's clear? Thanks Frank Frank, The $ in a currency format should also get printed. It is a view of the data, but consistent on screen and print. -- HTH RP (remove nothere from the email address if mailing direct) "Frank" <stratster68@IHATESPAMworldnet.att.net> wrote in message news:%23lA$94VRFHA.3988@tk2msftngp13.phx.gbl... > I want to insert a $ in column of cells. Use currency as the format, but I >...

cell to change colour at given date
I have a cell in a spreadsheet into which I type a date. When the pc clock shows that the date in the cell is more that 30 days old I want the cell to turn red. How would I do that. Basic to many of you I am sure, but to someone who doesn't use this side of Excel, virtually impossible ! (I'm using Excel 97 if that makes any difference - but I presume not) Thanks in advance Tobit LOL - My pleasure. Glad you got sorted, and thanks for the feedback. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP...

How to link cell for value in another cell of pivot table?
In Excel 2003, when I link a cell in pivot table, it copies Getpivotdata formular. And when I drag or copy this cell, again it copies the formular. Can you help how to just get the value, then be able to drag to link next cell and its value? thanks. There are instructions here for adding the Generate GetPivotData button to a toolbar, and toggling the feature on and off: http://www.contextures.com/xlPivot06.html JamesChoi wrote: > In Excel 2003, when I link a cell in pivot table, it copies Getpivotdata > formular. > And when I drag or copy this cell, again it copies th...

How to mass convert Filename.eml to Filename.msg while keeping them in original subdirectories?
Does anyone know of a utility that walks an explorer directory and subdirectory converting every .eml file into a .msg file? My company has used Outlook express for 4 years, and we often copied emails to various Windows Explorer directories. After copying, we would often rename the .eml file -- for instance change "please help.eml" to "John Doe needs help with Outlook Express crashes.eml". Each e-mail copied resulted in an .eml file like "Hey Santa, here is my xmas list.eml" Find File .eml reveals that we have 3000 such files spread over 100 subdirectories. ...

Item Decimal Places Change
Hello, We recently found an issue with GP 7.5 Inventory. We were able to change an item set to 2 decimal places to 5. This condition will not allow a sale on this part until the Decimal place (IV00101 and IV00105 DECPLCUR) is changed back to 2 decimals. The fix requires the running of check links on the Inventory tables. I have detailed the steps that caused this issue below: 1. Open Item Maintenance 2. Enter Part Number 3. Click Lookup icon 4. In the Item screen, highlight the desired item number 5. Click Open 6. In Item Inquiry Screen click Item Number link 7. Click Item Maintenance...

How do I locate and change the template for emails
fredman <fredman@discussions.microsoft.com> wrote: <nothing> State your Outlook version and use the big white space to ask your question in detail. -- Brian Tillman [MVP-Outlook] you can't change the template, you can use stationery. if you want more details, we need to know more information - such as your version of outlook and if you are using the outlook editor or word if you aren't using outlook 2007. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook ...

Changing Publisher Calendar
I have created a 2007 calendar using the 2003 version of Publisher. What I need is one continuous document with all 12 months, but what I have gotten instead is each month is on a tab at the bottom (like Excel does). Can I reformat this to make it one document, instead of being "chopped up" into 12 different documents? Or is there a way to create a 12 month calendar that is one continuous document? If your calendar pages are standard letter size, setup a page as a banner, copy/paste each month into the new publication and rotate 90 degrees. I don't know how helpful thi...

Sales tax changes #2
Is there any method of changing sales tax for an item or overall sale while in POS? My need to be able to charge sales tax based on the customer's location only when we will deliver the goods to them. This is state law that will go into effect in mid-2005 for Ohio. The majority of our sales are cash and carry so the sales tax rate is setup in Store Manager on a per item basis. I didn't think changing to a per customer basis made sense for my situation. Thanks Bob, You will have to change to on a per customer basis and set the ship to state for taxable items. Also you c...