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

[jproffer]

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?

 

[Fluff]

Rather than trying to catch the error, why not use something like this

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]

@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

 

[jproffer]

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]

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]

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:

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]

You're almost there, the 1st ' needs to go at the beginning

If Evaluate("isref('[" & wbName & "]" & NewName & "'!$A$1)") Then

 

[jproffer]

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.

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.

 

[Fluff]

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

If Evaluate("isref('[" & NameOnly & "]" & NewName & "'!$A$1)") = False Then
   Workbooks(NameOnly).Worksheets(OldName).Name = NewName
End If

 

[jproffer]

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"?