Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Capture error number

Posted on 2013-10-28
19
269 Views
Last Modified: 2013-10-28
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

0
Comment
Question by:Frank Freese
  • 10
  • 8
19 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 39606059
Your code won't generate an error, so you won't get an error number.
0
 

Author Comment

by:Frank Freese
ID: 39606092
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
 
LVL 46

Expert Comment

by:Martin Liss
ID: 39606094
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
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 

Author Comment

by:Frank Freese
ID: 39606113
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
 
LVL 46

Expert Comment

by:Martin Liss
ID: 39606134
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
 

Author Comment

by:Frank Freese
ID: 39606159
My objective is to not display the standard worksheet message but replace it with my own. I meant bypass the system message also.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 39606165
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
 

Author Comment

by:Frank Freese
ID: 39606193
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
 

Author Comment

by:Frank Freese
ID: 39606221
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
 
LVL 46

Expert Comment

by:Martin Liss
ID: 39606227
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
 
LVL 46

Expert Comment

by:Martin Liss
ID: 39606238
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
 

Author Comment

by:Frank Freese
ID: 39606309
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
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 39606364
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
 

Author Closing Comment

by:Frank Freese
ID: 39606433
thank you
You da man!
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 39606443
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
 

Author Comment

by:Frank Freese
ID: 39606461
the last
0
 

Author Comment

by:Frank Freese
ID: 39606474
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
 
LVL 46

Expert Comment

by:Martin Liss
ID: 39606488
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
 

Author Comment

by:Frank Freese
ID: 39606554
thanks for the followup
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question