Link to home
Start Free TrialLog in
Avatar of Frank Freese
Frank FreeseFlag for United States of America

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.

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

Avatar of Norie
Norie

Your code won't generate an error, so you won't get an error number.
Avatar of Frank Freese

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

Open in new window

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?
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

Open in new window

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.
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

Open in new window

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
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thank you
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
the last
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
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

thanks for the followup