Solved

Capture error number

Posted on 2013-10-28
19
267 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

864 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now