JugglerJAF
Active Member
- Joined
- Feb 17, 2002
- Messages
- 297
- Office Version
- 365
- Platform
- Windows
I have a file I've set up for a colleague that has some cells/columns with data validation and conditional formatting. Unfortunately, the user of the sheet can't seem to grasp the difference between "delete" which clears the contents of a cell and "clear" which clears everything, including the data validation and conditional formatting.
I think the problem stems from the user wanting to clear filters to show all values and accidentally clicking on "Clear" instead of "Sort & Filter > Clear". I've tried showing him the Ctrl+Shift+L shortcut to turn filters on/off, but I think this has just confused him even more!
Short of setting up the file with an "on open" or "before save" macro that restores the required validation and formatting to the appropriate cells, is there any other way of protecting a worksheet so as to allow for data to be entered, edited, or deleted (i.e. cell is cannot be locked), but to prevent the use of "clear"?
I think the problem stems from the user wanting to clear filters to show all values and accidentally clicking on "Clear" instead of "Sort & Filter > Clear". I've tried showing him the Ctrl+Shift+L shortcut to turn filters on/off, but I think this has just confused him even more!
Short of setting up the file with an "on open" or "before save" macro that restores the required validation and formatting to the appropriate cells, is there any other way of protecting a worksheet so as to allow for data to be entered, edited, or deleted (i.e. cell is cannot be locked), but to prevent the use of "clear"?