Morning all,
Does anyone have any idea how I can customise an Excel error message?
I have a locked sheet which, if an attempt is made to input anything into it, generates the bog-standard 'This sheet is locked blah' and then prompts the user to unlock the sheet etc.
What I would prefer to see is an alert that tells the user the sheet is for information only and please make amendments elsewhere.
I'd be glad of any suggestions.
Zazie
p.s am using excel 97
Hi zazie, and Welcome to the Forum.
You can suppress the 'This sheet is locked..blah' message and replace it with your own Message Box like this:
1. Unprotect the sheet.
2. Right-click the sheet tab and select "View Code".
3. Select Worksheet from the left-hand drop-down at the top of the code window - this will insert this procedure:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
4. Add this line in the gap:
MsgBox ("This Sheet is locked. Please make changes on Sheet 2")
or you can change Sheet 2 to whichever sheet the User is allowed to change.
5. In the Right-hand drop-down at the top of the code window, Select "Activate" and amend the event procedure to read:
Private Sub Worksheet_Activate()
Application.DisplayAlerts = False
End Sub
6. Go back to your sensitive sheet and Protect it again.
Now if you select any cell in the worksheet you'll get your custom message.
Hope that helps! :wink2:
Chris
Just thought of something; make sure they go to Sheet 2 by doing this in the Selection_Change event:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox ("This Sheet is locked. Please make changes on Sheet 2")
Sheets("Sheet2").Activate
End Sub
See if that suits.
Chris
Thanks very much Chris - you were quick!
I had just been tinkering about with the Worksheet_SelectionChange bit so feel reassured that you're thinking along the same lines.
I have got it to the point where it does the custom message easily enough. I have a problem though: the program needs to write to the sheet - I need vba to have write access but not the user.
Any further ideas?
Many thanks,
Zazie
..maybe something along the lines of
If input via user interface then custom message
end if
??
Yeah, just Unprotect it before you do stuff to it, then Protect it again when you hand control back to the Workbook. Something like this:
Private Sub Do_Stuff_On_The_Sheet()
ActiveSheet.Unprotect
.....Do Stuff To It
ActiveSheet.Protect
End Sub
How's that?
Chris
here's an alternative
On Error GoTo ProtectMe
ThisWorkbook.Unprotect Password:="secret"
'YOUR CODE
ProtectMe:
ThisWorkbook.Protect Password:="secret"
Is it really necessary to unprotect the sheet and reprotect it to get round this?
I wondered if there was something like when you protect the sheet in code:
Sheet21.Protect Password:=whatever, userinterfaceonly:=True
..so the code can interact but just not the user.
Sure you can, Ziz! Try the attached.
If you click anywhere in Sheet1 the MsgBox directs you to Sheet2 then activates it. Click Sheet1's tab and A1 contains a random number between 1 and 100, but you still can't select a cell.
Hope that's close to what you want! :wink1:
Thank you very much for your time.
I am puzzled.
I have looked at your example and played with the workbook. Perhaps if I explain more, you'll see what the problem is (or in what way I'm being stupid..)
All my sheets in the book are password protected when the code runs. This protection is userinterface only as the vba has to make entries and changes to locked cells. This is working fine.
The problem occured as I wanted to customise the error message when the user attempted to input into a locked cell. The selection_change event works well but I cannot see how to pass the crucial parameters that permit changes via code but prevent changes via userinterface. Does this make sense?
The program is scooting through and creating a discrepancy report based on various tests. Clearly the code needs to enter details but the user should be prevented.
The selection_change event currently pops up the msgbox for every change - whether it is a change we want to see or a non-permitted user change.
Anyone know what I'm missing?
Thanks again,
Zaz
Don’t have an account yet? Register yourself now and be a part of our community!
