custom error message in popup window for protected cell?

[jayd4wg]

Here's one to stump you guys...

I have a worksheet for my employees and I only want them to be able to alter/enter data in certain cells...that much I have covered.

NOW what I want to do is replace the standard "the cell or worksheet your are trying to change is read-only and therefor cannot be changed blah blah blah" to say something like "HEY DORK...you can only fill in certain fields, quit trying to re-write history!" so they will quit trying to redesign my spreadsheets.. Any thoughts?

 

[Lokai]

Most users use the Page Up /Page Down & arrow keys to navigate a spreadsheet. Using the solutions above the user cannot use the Page Up, Page Down or the arrow keys to move thru a large worksheet. An error message pop ups every time they try to move thru the spreadsheet. I'd like them to see the data just not be able to change it - & have a custom error message if they try to make a change.
Yes, they can still use the scroll bars only but that is a constraint on typical behavior.

 

[lenze]

Maybe something like this.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If Target.Locked = True Then
MsgBox ("your message")
Target.ClearContents
End If
Application.EnableEvents = True
End Sub

Why can't your users use the TAB key?

lenze

EDIT: As an added thought, if your cell is locked and the sheet is protected, you may have to disable alerts before the IF statement. Don't forget to enable them before exiting the sub.

 

[Lokai]

Thanks lenze,
I have used that technique in the past & may use it here. With regards to your TAB question, in general I like solutions that don't interfere with a user's typical behavior which would include using arrow keys.

 

[Zack Barresse]

.. in general I like solutions that don't interfere with a user's typical behavior which would include using arrow keys.

As much fun as it is to leave a user be in his/her (possibly) bad habits, it's so much more fun to break them out of their paradigm!!

 

[CaptainKen]

I've tried all 3 examples of code in this thread, but the only one that doesn't error is from post #3.

After trying post #12 code, I get this error when a locked cell is selected and after the custom message is displayed:
"Runtime error 1004: The cell or chart you are trying to change is on a protected sheet. To make a change unprotected sheet. You might be requested to enter a password."

NOTE: When using the code from post #12, doesn't matter if I select End or Debug or clicking the VBA Reset button, I have to close and reopened Excel in order to get any code to work.

After trying post #4 code, I get this error when any locked/unlocked cell is selected:
"Runtime error 1004: Method 'Range' of object'_Worksheet' failed."

Therefore, the only code that works is from post #3, but it moves the focus to A10 after clicking OK, rather then keeping it on the originally selected cell. It also doesn't allow for using the PgUp, PgDown and Arrow keys to navigate as mentioned in post #8.

I was able to modify the code from post #3 to keep the focus on the cell that triggered the error as show here, but it triggers the error when the cell is selected, rather than only if an attempt to modify it is done.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim goodRng As Range
    Application.EnableEvents = False
    If Target.Locked Then
        Application.EnableEvents = True
        MsgBox "Stop!" & vbNewLine & vbNewLine & _
            "The cell(s) you are trying to alter are protected" & vbNewLine & _
            "and should not be altered without prior" & vbNewLine & _
            "authorization." & vbNewLine & vbNewLine & _
            "Thank you," & vbNewLine & _
            "Management", vbCritical, "STOP!"
    End If
    Application.EnableEvents = True

End Sub

My objectives:
1) Range is entire sheet
2) As mentioned in post #8, I want the users to be able to use the PgUp, PgDown and Arrow keys to navigate. Therefore, only trigger custom error if they attempt to modify a locked cell.
3) Leave the user on the cell that generated the error