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
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
9,999
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.)
 

cfree36

Board Regular
Joined
Oct 5, 2005
Messages
175
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...
 

cfree36

Board Regular
Joined
Oct 5, 2005
Messages
175
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:
 

nomeci

New Member
Joined
Nov 22, 2005
Messages
5
I apologize - I am out of practice with using question forums and obviously wasnt thinking...

Everyone here uses Excel 2000
 

nomeci

New Member
Joined
Nov 22, 2005
Messages
5
cfree36

your validation suggestion may be just what I need! i had not thought of that - thank you so very much!
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
9,999
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.
 

nomeci

New Member
Joined
Nov 22, 2005
Messages
5
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.
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
9,999
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.
 

nomeci

New Member
Joined
Nov 22, 2005
Messages
5
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!
 

Forum statistics

Threads
1,077,783
Messages
5,336,304
Members
399,074
Latest member
rlong98

Some videos you may like

This Week's Hot Topics

Top