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]

Try putting the extension on the workbook name. It depends on your system settings as to whether you need it or not.

 

[jproffer]

Ok, I tried several different variations, while we're at it. Here they are:

With the extension:
If I use a bad (already used) name, it works...it goes to Else and shows my message.
If I use a good (not yet used) name, it shows an error...Type Mismatch.

Without the extension:
If I use a bad name, it goes to the rename line and tries/fails to rename it and pops up the native "sheet name already in use" message.
If I use a good name, it renames it and ends the routine as it should.

Other than the type mismatch with the extension IF I use a good name, I can see the consistency. With Ext, it goes to Else...without Ext it goes to Rename line. But still the type mismatch is throwing me for a curve......and really the entire thing is throwing me for a curve, since with a good name it should go to the Rename Line and with a bad name it should go to Else and show my message.

BTW, thank you for all your help and your continued help. It's frustrating as he.....heck

 

[Fluff]

Not sure what's happening I'm afraid.
I've only ever used it on the same workbook, so not sure if the fact that it's looking at a different workbook, is throwing it off.

 

[jproffer]

No worries. I'll keep tinkering with it and see if I can (accidentally, no doubt ) find a way that works. If I do, I'll post back here for future reference.

 

[Fluff]

Try this workround

   Workbooks(NameOnly).Activate
   If Evaluate("isref('" & NewName & "'!$A$1)") Then
      MsgBox "sheet Exists"
   Else
      Sheets(OldName).Name = NewName
   End If

 

[jproffer]

I just got done messing around with it some more. This appears to work properly....so far. I'll keep your workaround in my pocket though, just in case

For i = 0 To Me.LstOldName.ListCount - 1
    OldName = Me.Controls("TbOldName" & i).Value
    NewName = Me.Controls("TbRename" & i).Value
        On Error Resume Next
            Workbooks(NameOnly).Worksheets(OldName).Name = NewName
    Next i
    
    If Err.Number <> 0 Then
        GoTo errmsg
    End If

What's weird is, I'm (ALMOST) positive that's what I tried originally and it didn't work like it does now. Who knows....I'm quite sure it's something I did differently between then and now. At any rate, thank you so much for all your help on this matter.

If I'm ever in Chippenham, I'm buying!!

 

[joeu2004]

This appears to work properly [....]

For i = 0 To Me.LstOldName.ListCount - 1
    [....]
        On Error Resume Next
            Workbooks(NameOnly).Worksheets(OldName).Name = NewName
    Next i
    
    If Err.Number <> 0 Then
        GoTo errmsg
    End If

That depends on your definition of "work properly".

The code will go to errmsg only if an error occurs on the last iteration of the loop, because On Error implicitly does Err.Clear.

(And that might explain why "it didn't work [before] like it does now".)

If your intent is to stop on the first error, you might as well do On Error GoTo errmsg outside the loop.

On the other hand, if your intent is to continue renaming worksheets despite some errors, making note of one or more errors afterwards, I would suggest something of the following form

Dim n as Long
[....]
On Error Resume Next
n = 0
For i = 0 To Me.LstOldName.ListCount - 1
    OldName = Me.Controls("TbOldName" & i).Value
    NewName = Me.Controls("TbRename" & i).Value
    Workbooks(NameOnly).Worksheets(OldName).Name = NewName
    If Err <> 0 Then n = n+1: Err.Clear
Next i
    
If n > 0 Then GoTo errmsg

It might be note that n does not have to be a counter. It could simply be type Boolean, which is set to True for Err <> 0.

But with a counter, your error message could indicate how many failures occurred.

 

[joeu2004]

PS....

If your intent is to stop on the first error, you might as well do On Error GoTo errmsg outside the loop.

Well, there are consequences of that implementation that you might not understand. An implementation that you might understand better is a minor change to yours, to wit:

On Error Resume Next
Workbooks(NameOnly).Worksheets(OldName).Name = NewName
If Err <> 0 Then Exit For

 

[jproffer]

Hmm...interesting. And honestly I don't know if it was the last iteration of the loop or not (if you say it had to be, I believe you...because I didn't pay attention. It was getting late and I was just glad it "worked" enough for me to leave it)

I'll have to check that out tomorrow at work, and I may have to try to implement one of your ideas. Many thanks joeu2004