In my testing, I found another "flaw" with the approach I'm pursuing with your advice Peter (and again, thank you!). Here's the scenario.
I have a worksheet that functions as a dynamic form for users to view and manipulate only certain portions of the worksheet. It extracts data from other worksheets and presents it to the user. But within this data, only certain portions are accessible to the user to actually edit. For these reasons, it's nice to have the ability to disable a user from being able to select a cell. Certianly with a protected sheet and locked cells, the most that will happen is an interrupting message, but for the ease of navigation and not implying to the user that a cell is editable, disabling the click on locked cells is preferred.
However, there are ranges of this populated data that although can't be edited (changed or manipulated), the ability to double click of a cell (in essence selecting that value) will call up a routine which will provide additional information for the user. For simplicity, lets just say double clicking a cell A14 will display a simple message ("Aha you want more information on this cell's value"). We don't want the user to be able to edit the value in A14, but we want an event that will pull up additional code for that value.
Here is my code thus far ...
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Not Intersect(Target, ws_master.Columns(1)) Is Nothing Then
MsgBox "RID number change - delete?"
End If
End Sub
So, on my protected sheet, with users able to select locked and unlocked cells, the cells in column A are coded to be locked. When the user double clicks on a cell in column A, they get the message (+1). They are unable to edit the values in the cell though (+1).
However, these are the unfavourable consequences to this method:
1) the user can click all over the place now, in areas where they shouldn't try to edit. The result is Excel's default message. An annoyance, and a loss of a fraction of time. Optimally to be able to prevent the user clicking would be best, but would settle with no message, or a custom message instead of the generic one.
2) the user can click on any cell in column A, whether it has a value or not. The user should be restricted to being able to only select on populated cells in that column. Running code that is expecting a value on an action to a cell that doesn't, will create errors. I suppose I could build into the doubleclick event code to end the routine if the value of Ax is null.
So, I share this in hopes that either an alternate solution might be revealed, find a different approach, or work within and accept the limitations.