Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Capture error number

Posted on 2013-10-28
19
Medium Priority
?
276 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 36

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 50

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 50

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 50

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 50

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 50

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 50

Accepted Solution

by:
Martin Liss earned 2000 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 50

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 50

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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 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.

572 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