0

Is there a way to allow users to click on, but not edit, the contents of a cell? I know I can tinker in the settings in Review->Protect Sheet with the "Select Locked Cells" option. But here's the issue: I want to restrict the user from clicking on every other locked cell, just not date cells. Reason is that I want the user to be able to click on a date cell and have a dialog box pop up where they can select a date from a calendar, but not directly edit the cell. So, in summary, I want three types of cells:

  1. editable, clickable cells
  2. noneditable, clickable cells
  3. noneditable, nonclickable cells

Is there some sort of middleground I can use to set some cells as option #2? I've tried various tricks with Application.Undo in the sheet events (which might make this question more fitting for Stackoverflow since that involves VBA) but so far I haven't had any luck.

  • For every other locked cell, you can tick "Select Locked Cell" to make it clickable. What's the problem with this? – Vylix May 11 '17 at 2:28
1

If you make a cell locked, you can't use a date picker either.

Given that this will require VBA to work, you do not need to worry about allowing selecting locked cells. You will need to add buttons or make VBA understand that it has to do something when the user clicks on a cell, and then in the macro temporarily remove the protection, change the content of the cell, add the protection back.

Given that SuperUser is not a "Please write me a script" kind of service, this is as far as my answer can go. If you start working on your VBA macro and you have questions, post your code as a new question and we can help you locate where things go wrong.

Don't forget, you can simply press the record button in excel, perform the action of your choice, press the stop button, and you have your macro. Then you need to edit it so it becomes a dynamic macro (works for any cell etc).

If VBA really is not an option, consider the following: Make all the date cells unlocked, and protect the worksheet. Users can freely click and edit those cells, but not the rest. Using Data validation, you can make sure they enter a date only.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.