We use a macro-enabled workbook for our field personnel to fill out reports digitally where:

  • cells for data entry are not locked
  • sheet protection is set to "Select unlocked cells only"

Thus, the guys can get into and edit only the unlocked cells. I have code that runs whenever certain cells are right-clicked (places a checkmark, inserts "Pass" or "Fail", etc).

Here's the problem:
If the user right-clicks on one of these unlocked cells (ie: to place a checkmark) then clicks elsewhere in a locked cell (perhaps by accident, adjacent to another unlocked cell on the other side of the form), the target is still at the first cell. This means the code executes the right click event on the target, which is still on the other side of the form. This is tricky, because he's not looking at that last cell he clicked on when it changes his checkmark to an "X" (or whatever my code is set to do on that type of cell). This has the potential of changing something that was "Acceptable" to "Rejectable" or vice versa. Can't have that!

Is it possible to detect when the user attempts to right click on a locked cell in a protected sheet? If a message box can be made to pop up and say "You're not allowed in this cell, sorry bud!", then I can restrict my BeforeRightClick macro to only run if the cell he's trying to right-click on is unlocked.

Again, this sheet is protected for selection of unlocked cells only, so checking the target range isn't an option.

Thanks!