Frank Freese
asked on
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.
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
Your code won't generate an error, so you won't get an error number.
ASKER
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?
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
ASKER
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.
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?
Can you describe more fully what you'd like to do?
ASKER
My objective is to not display the standard worksheet message but replace it with my own. I meant bypass the system message also.
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
ASKER
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.
ASKER
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?
Change line 15 to Debug.Print Err.Number and then look in the Immediate Window, or you could change it to MsgBox Err.Number.
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
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you
You da man!
You da man!
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
Marty - MVP 2009 to 2013
ASKER
the last
ASKER
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
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
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
ASKER
thanks for the followup