Can you lock specific cells but NOT lock the formatting?

nomeci

New Member
Joined
Nov 22, 2005
Messages
5

I have a worksheet with a lot of formulas in it that i want to protect so the end user does not continually type over the formulas. Originally I had made sure that the cells with formulas were locked and protected the sheet - leaving the cells that the end user would need to change unlocked. Unfortunately with the workbook protected, the end user is not able to adjust the width of the columns or any other formatting like that. This is a function that she needs in order for it to print it out correctly.

I finally gave in and unprotected the workbook, but now i have to go back in randomly and correct formulas because they were accidentally deleted or typed over... i dont mind doing it, but i usually the end user doesnt realize it is not working properly until after they have sent out all the schedules and someone calls the wrong person because the number listed is not the correct one.

Is there a way to only lock the cells (and their content) and not have it affect the formatting abilities? I hope I have explained this well enough...

Thank you for taking the time to read this...

Nomi
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
You left out a key bit of information. What version of Excel are you and your users running? (I'm assuming it is not 2002 or better because protection offers this type of control.)
 
Upvote 0
What version of excel are you using?

In excel 2003 you can choose Tools / Protection / Protect Sheet.

then in the Allow all users of this workseet to:
choose what you want them to be able to do...
 
Upvote 0
If you are using an earlier version of excel you can use data validation to help stop users from typing over your formula.

Goto Data / Validation
On the Settings Tab
In the Allow box choose Custom
In the Formula box type =""

On the Error Alert Tab
In the Error Message box type what you want the validation to tell the user who types in your cell. Example: FORMULA CELL.

One issue with this is they can either delete the cell.... or copy and paste over it... but, if typing in the cell is the issue this works nicely.

:cool:
 
Upvote 0
I apologize - I am out of practice with using question forums and obviously wasnt thinking...

Everyone here uses Excel 2000
 
Upvote 0
cfree36

your validation suggestion may be just what I need! i had not thought of that - thank you so very much!
 
Upvote 0
Validation may indeed work for you. If you are comfortable with VBA, you might have a look at this thread. You could alter the code to scan for cells where .formula <> .value and pop a warning. But note there are limitations to this approach as well.
 
Upvote 0
I have started to learn VBA but between work and family and school I havent been able to play with it in months so it is like starting all over again. I am trying to use the code that is shown in the other thread, but am not sure how to change it to work the way I want it to... while I continue to play with that, can you tell me about the limitations that would be present if i chose this route?

thanks again for all your help.
 
Upvote 0
It does not trap for deleting cells/rows/columns. Also, you would need to add to that code to restore values/formulas where the changes were accepted (as if clicking "redo"). Because the OP in that thread merely wanted to protect against deletion and not over-writing the "redo" action was simpler.
 
Upvote 0
thank you so much for takng the time to help me with this! I will play with this again as soon as i finish putting this schedule together...

Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top