Solved

Capture error number

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

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 45

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 45

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 45

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 45

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 45

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 45

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 45

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
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;…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

708 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

18 Experts available now in Live!

Get 1:1 Help Now