PDA

View Full Version : Default Messages



cosmarchy
07-01-2010, 12:48 AM
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
07-01-2010, 01:40 AM
Don't think so.

Paul_Hossler
07-01-2010, 02:16 PM
Sort of what you're looking for, not perfect nor bullet-proof

Put this in the appropriate worksheet code module


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


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

Paul

GTO
07-02-2010, 08:16 AM
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