Capture error number

Folks,
What I am trying to do is replace one system error descrption with my error description. I was hoping to capture the error code that says"The cell or chart that you are trying to change is protected and therefore read only." I am guessing but this appears to be a system generated respone. I though if I could capture the error number I could by pass the message and exit the sub but I can't seem to get the error number code.

Sub NotifyUserCellProtected()
If ActiveSheet.ProtectContents = True And ActiveCell.Locked = True Then
    MsgBox "That cell is protected and cannot be changed!"
End If

On Error GoTo errNumber

NormalExit:
Exit Sub

errNumber:
MsgBox Err.Number
Resume NormalExit

End Sub

Open in new window

Frank FreeseAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Martin LissConnect With a Mentor Older than dirtCommented:
Is your aim here to be able to update a protected cell or chart via code even though it's protected? If so you can do this at the start of the macro - ws.Protect UserInterFaceOnly:=True  where ws is set to the sheet in which you want to make the change, and ws.UserInterFaceOnly:=False at the end of the macro.

Or if you just want to ignore the error then put On Error Resume Next at the top of the macro.
0
 
NorieVBA ExpertCommented:
Your code won't generate an error, so you won't get an error number.
0
 
Frank FreeseAuthor Commented:
I thought of "DisplayAlerts = True" when the worksheet is Activated and enter in my own message and when the worksheet is Deactivated the "DisplayAlerts = "False". Would that work?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Martin LissOlder than dirtCommented:
What do you mean by "pass the message"?
Sub NotifyUserCellProtected()

On Error GoTo errNumber

If ActiveSheet.ProtectContents = True And ActiveCell.Locked = True Then
    MsgBox "That cell is protected and cannot be changed!"
End If



NormalExit:
Exit Sub

errNumber:

If Err.Number = 123 Then' or whatever the error number you are getting is
    MsgBox Err.Number & " (" & Err.Description & ")" & " generated in NotifyUserCellProtected"
    Resume NormalExit
End If

End Sub

Open in new window

0
 
Frank FreeseAuthor Commented:
I do not think what I am asking for from Microsoft is possible. As I understand it DisplayAlerts = False only is true within the confines of the macro, and once completly execute Microsoft resets the DisplayAlerts = True. But then, that's why we have the experts.
0
 
Martin LissOlder than dirtCommented:
You may have missed my question which was "What do you mean by "pass the message"?".

Can you describe more fully what you'd like to do?
0
 
Frank FreeseAuthor Commented:
My objective is to not display the standard worksheet message but replace it with my own. I meant bypass the system message also.
0
 
Martin LissOlder than dirtCommented:
Then change my code to this. BTW note where the On error... line is.

Sub NotifyUserCellProtected()

On Error GoTo errNumber

If ActiveSheet.ProtectContents = True And ActiveCell.Locked = True Then
    MsgBox "That cell is protected and cannot be changed!"
End If



NormalExit:
Exit Sub

errNumber:

If Err.Number = 123 Then' or whatever the error number you are getting is
    MsgBox "This is my message"
    Resume NormalExit
End If

End Sub

Open in new window

0
 
Frank FreeseAuthor Commented:
The problem is I can't capture the error number. In my first attempt if was able to capture the error number then I go forward as you suggested. I haven't been able to find a lisitng of those error code numbers.
0
 
Frank FreeseAuthor Commented:
Well, I found Microsoft's error number list and associated messages and there is not a error number I can capture to customize the error. RATS! But maybe someone has a solution?
0
 
Martin LissOlder than dirtCommented:
Change line 15 to Debug.Print Err.Number and then look in the Immediate Window, or you could change it to MsgBox Err.Number.
0
 
Martin LissOlder than dirtCommented:
Put this code somewhere and run it. It captures error 11 (division by zero) and shows a custom message.

Sub Test()

On Error GoTo ErrorRoutine

MsgBox 7 / 0

Exit Sub
ErrorRoutine:

If Err.Number = 11 Then
    MsgBox "This is a customized message for when division by zero (err.number 11) happens"
End If
End Sub

Open in new window

0
 
Frank FreeseAuthor Commented:
We're on the same page, however, maybe it is not an "error" but a system response to a worksheet that is protected and the response cannot be changed. I did try your code and got the expected error.
0
 
Frank FreeseAuthor Commented:
thank you
You da man!
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help. Out of curiosity which of the two suggestions in that post helped?

Marty - MVP 2009 to 2013
0
 
Frank FreeseAuthor Commented:
the last
0
 
Frank FreeseAuthor Commented:
Martin,
I have completed that massive workbook and now I am verifying each worksheet. When done, I'll shoot you a copy. Without your help as well as many other Experts this "masterpiece" would not have been possbile.
Frank
0
 
Martin LissOlder than dirtCommented:
Then if appropriate you should do the following. Note lines 3, 11 and 17.

Sub Test()

On Error GoTo ErrorRoutine

' Here would be some lines of code that could cause an err that you
' DON'T want to ignore
Line 1
Line 2

' You want to ignore this error
On Error Resume Next
MsgBox 7 / 0

' Turn normal error checking back on because there are lines
' of code that follow that could cause an err that you don't
' want to ignore
On error Goto 0
Line 98
Line 99

Exit Sub
ErrorRoutine:

If Err.Number = 11 Then
    MsgBox "This is a customized message for when division by zero (err.number 11) happens"
End If
End Sub

Open in new window

0
 
Frank FreeseAuthor Commented:
thanks for the followup
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.