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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.