Help with data validation

tim963

Board Regular
Joined
Aug 20, 2002
Messages
58
Using excel ’97 why want data validation work with freeze panes, the show message when cell is selected will not work when I use freeze panes, also what would you put in the settings tab to keep someone from writing over or deleting a formula in a cell. Thanks
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
On 2002-09-22 15:41, tim963 wrote:
Using excel ’97 why want data validation work with freeze panes, the show message when cell is selected will not work when I use freeze panes, also what would you put in the settings tab to keep someone from writing over or deleting a formula in a cell. Thanks

Tim,

The data validation / freeze panes is a known bug that's been fixed for xl 2000 & above - see:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q236620

re the second Q, there is no single 'setting' that will do this. There are a number of more-or-less robust alternatives (less robust = use validation, more robust = use VBA). Plenty of examples here (search for 'protect cells' or similar). Post back with more details of what you need - what level of maliciousness are you trying to protect against? Average, careless users?...


Paddy

EDIT: typos
This message was edited by PaddyD on 2002-09-22 16:58
 

tim963

Board Regular
Joined
Aug 20, 2002
Messages
58
Paddy, I am just trying to keep the careless users from deleting the formulas in a column
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Try this:

1) select the cells you want user to be able to change
2) go to Format | Cells | Protection. Uncheck the 'Locked' box.
3) Select the cells you want the user for keep out of. (If these are formulas, one way is to hit F5 to get the go to.. box, the select special...formulas)
4) go to Format | Cells | Protection. Check the 'Locked' box if it isn't already.
5) go to Tools | Protection | Protect Sheet (or workbook, if appropriate).
6) Enter a password if you want (don't forget to remember it!)

Voila...protected formulas.

Paddy
 

Forum statistics

Threads
1,181,531
Messages
5,930,437
Members
436,738
Latest member
JFry

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
Top