On error resume next........but I want to know about it and act on it. Possible?

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
Hello again,

I have some code that renames a sheet or sheets, depending on user input. In case of an already used name, I have "On Error Resume Next" because...well honestly there's nothing they can do about it, code wise...they need to pick a different name. But I would like to know about the error so I can pop a message that one or more sheets was not renamed.

I've tried " If Err.Number <> "" " and " If Err.Number <> 0 " but with no luck. SO, how does one determine that there was an error without that error bugging out the code?
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,138
Office Version
  1. 365
Platform
  1. Windows
Rather than trying to catch the error, why not use something like this
Code:
Sub chk()
   Dim sht As String
   sht = "pcode"
   If Evaluate("isref('" & sht & "'!a1)") Then
      MsgBox "sheet Exists"
   Else
      Sheets.Add.Name = sht
   End If
End Sub
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,975
Office Version
  1. 2010
Platform
  1. Windows
@jproffer.... After On Error Resume Next, testing If Err<>0 or If Err.Number<>0 should detect an error.

However, you might need to do Err.Clear before the code block for which you want to detect an error. For example:

On Error Resume Next
x = 1 / 0
If Err<>0 then Msgbox "bad x"
y = 2 * 3
If Err<>0 then Msgbox "bad y"

That code is flawed because the error detected at the end ("bad y") is really the div by zero ("bad x"). The correct code is:

On Error Resume Next
x = 1 / 0
If Err<>0 Then Msgbox "bad x": Err.Clear
y = 2 * 3
If Err<>0 Then Msgbox "bad y" : Err.Clear
 
Last edited:

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
Thanks to both of you. I'll try both ideas first thing tomorrow at work.

Fluff, one question:
If I'm reading it right, what your code is doing to see if Cell A1 on a certain sheet exists (isref...right?) and if it doesn't, then needless to say the sheet itself doesn't exist. Am I close?

Joeu2004: I'll try that also, even if both work it's always good to learn alternate ways. Thank you for that.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,138
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Fluff, one question:
If I'm reading it right, what your code is doing to see if Cell A1 on a certain sheet exists (isref...right?) and if it doesn't, then needless to say the sheet itself doesn't exist. Am I close?
Your spot on. :)
 

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
Ok, Fluff.... I'd like to include the workbook name in the evaluate line, if possible. I think I'm close. I basically entered that function on a sheet, selected a different workbook, and cell A1 and then (tried, with no luck yet) to adapt that to code with variables, etc. What I tried that I think is closest to right is this:

Code:
If Evaluate("Isref([" & WBName & "]'" & NewName & "'!$A$1") Then

WBName is obviously the name of the workbook the user is working with.
NewName is the name they've typed into a textbox that they want to change an OldName (not used here) to.

I get a type mismatch error, which I'm assuming has something to do with some of the " [ ] " or " ' " that are used.....

Any thoughts?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,138
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You're almost there, the 1st ' needs to go at the beginning
Code:
If Evaluate("isref('[" & wbName & "]" & NewName & "'!$A$1)") Then
 

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
Ok, gotcha. So the apostrophes wrap the workbook name AND the sheet name and the WB name is separated again with the brackets?

I tried that but it always goes to the naming line regardless of an already used name or not.

Code:
For i = 0 To Me.LstOldName.ListCount - 1
    OldName = Me.Controls("TbOldName" & i).Value
    NewName = Me.Controls("TbRename" & i).Value
            If Evaluate("isref('[" & NameOnly & "]" & NewName & "'!$A$1)") Then
                GoTo errmsg
            End If
        Workbooks(NameOnly).Worksheets(OldName).Name = NewName
    Next i

This is what I'm using. The IF statement with the evaluate line always goes to End If. Am I missing something? (which is kind of a silly question...obviously I AM missing something or it would work :) )

EDIT TO ADD: Just for the sake of complete information, I've tried "If Evaluate........ = True" and false and neither seem to recognize that the sheet already exists. Maybe it's a matter of WHERE I put the =True/=False...inside the quotes or outside...I don't really remember and I don't know where they should be, IF that's even the problem.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,138
Office Version
  1. 365
Platform
  1. Windows
What is the value of NameOnly & newName?

The evaluate will return either true or false & so you don't need to test for it.
Depending on what errmsg is & does you could write it like
Code:
If Evaluate("isref('[" & NameOnly & "]" & NewName & "'!$A$1)") = False Then
   Workbooks(NameOnly).Worksheets(OldName).Name = NewName
End If
 

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
At the moment, NameOnly = "test" (the name of the workbook with the .xlsx trimmed off) and NewName = joe2 (the name I'd like to rename the sheet TO...but also the name of another worksheet, which is, as you know, what I'm trying to test for)

Yep, just tried it. Even if the name is already taken, it goes to the rename line (of the code you posted most recently). If the name is taken shouldn't it skip the rename line and loop to the next "i"?
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,940
Messages
5,598,975
Members
414,270
Latest member
skipolmsted

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top