Default Messages

[cosmarchy]

Hi

When a sheet is protected, a default message appears when a user tries to enter data. Is there any way to replace this text with my own? I would rather create my own message which is more relevant, descriptive and helpful to the end user (for my specific purposes anyway!!) and was wondering whether anyone knows of how to do this?

Thanks

[Bob Phillips]

Don't think so.

[Paul_Hossler]

Sort of what you're looking for, not perfect nor bullet-proof

Put this in the appropriate worksheet code module

[VBA]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "Sheet is protected, and you can't do this"
End Sub
[/VBA]

When the user clicks a cell (i.e. Changes the selection) the message will display

Paul

[GTO]

Not super-well tested, but this seems to work w/no failure noted.

I took the code offered by Jaafar Tribak in post #1 at:

http://www.mrexcel.com/forum/showthread.php?t=402721

I changed the last sub to:

'// Hopefully I didn't goof the mod I made.         //
Private Sub Sheet_KeyPress(ByVal KeyAscii As Integer, _
                           ByVal KeyCode As Integer, _
                           ByVal Target As Range, _
                           Cancel As Boolean)
    
Const MSG As String = "The keyboard is going to shock you" & vbNewLine & _
                      "if you touch my protected sheet!"
Const TITLE As String = "Stop That!"
    
    If KeyAscii > 0 Then
        MsgBox MSG, vbCritical, TITLE
        Cancel = True
    End If
End Sub

The above as well as Jaafar's remaining code goes in a Standard Module.

In the ThisWorkbook Module, I used:

Option Explicit
    
Private Sub Workbook_Activate()
    Call Workbook_SheetActivate(ActiveSheet)
End Sub
    
Private Sub Workbook_Deactivate()
    Call StopKeyWatch
End Sub
    
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    
    If Sh.ProtectContents _
    Or Sh.ProtectDrawingObjects _
    Or Sh.ProtectScenarios Then
        Call StartKeyWatch
    Else
        Call StopKeyWatch
    End If
End Sub

...to start/stop it all.

Mark