VBA Worksheet Tab Rename Macro Causing Excel Infinite Loop

Howdy,

So, I found the following "Worksheet Tab Rename " macro code posted on MrExel by member VOG, in the following thread:

http://www.mrexcel.com/forum/excel-q...s-formula.html

Please Login or Register  to view this content.

Nobody over there seems to be able to replicate my issue or determine what is causing my issue, so I thought that I should post here in the hopes that, since I can actually attach my spreadsheet here, folks could open it & replicate my issue.

The macro code works perfectly, except when I make a copy of the Worksheet (by right-clicking the Tab & selecting "move or copy") and then paste it in the workbook, it starts an infinite loop which I must kill Excel in order to get out of.

I know it's the macro code that is the issue, or that is triggering the issue, because when I have the code commented out, I can make a copy of the Worksheet.

My "A1" in the code is "S47" in my sheet, and it is calculated from a formula. Also, the cell & formula get updated when a user selects a choice from a drop-down list in a different cell. I want the user to select the choice from the drop-down list, and then that choice gets used as the name for the tab.

If he copies the sheet, he will then select another (different) choice from the drop-down list on that sheet. Each sheet that he copies must have a different choice selected, so at no time will there be more than 1 sheet with the same choice selected (except of course on the initial "copy & paste" of the tab, which I think is causing the infinite loop).

If anyone can chime in w/ a solution, as well as how to troubleshoot / debug this, any & all help would be much appreciated.

Thanks,
Rob
Excel 2007
Windows 7

No need to use the Worksheet _Calcule event procedure. That is triggered when any formula is calculated.

Have the combox event procedure rename the sheet based on the formula cell, or create the name in the combox code.

Please Login or Register  to view this content.

AlphaFrog,

Thank you for your assistance!! I moved the macro to the Combobox procedure, and it works!! However, even though it does NOT throw me into an infinite loop, it does give me a Run-time error 1004: Cannot rename a sheet the same name as another sheet...

The interesting thing is that it did create a new sheet, and just added (2) to the end of it, so I don't know why it still errored out... Is there some way to implement an error check, or otherwise remove / avoid this error?

If I click "End" on the Error message, it goes away & the copied sheet renames, everything looks good. However, if I click "Debug" - it takes me to this part of the code:

Please Login or Register  to view this content.

Again, thank you - I feel I am ALMOST there!!!

When you copy a sheet it's going to automatically name it the same name as the original with a (2) at the end of it.

I'm guessing you still have some legacy renaming code still in the Worksheet_Calculate procedure or some other event procedure?

I'm not exactly sure what you want to happen and the order you do things. Do you copy then rename or do you rename then copy? Do you want the (2)? What do you want if the same supplier is selected a 2nd time and the sheet is copied again.

I checked & do not have legacy code laying around. Is the reason I'm getting an error because of the "On Error Resume Next" or "On Error Goto 0" (admittedly, I don't really know what these 2 statements do).

And since each sheet will be a quote we receive from a supplier, there will never be a time where there is more than 1 sheet w/ the same supplier, because the whole reason for copying the sheet is if we have more than 1 supplier who has supplied us w/ quotes - thus, each sheet would be the quote from a unique supplier, hence a unique tab name.

As far as how the events should occur: The user opens the sheet & fills out the stuff in yellow. When the user selects the supplier via the drop-down, the tab should rename. If there is more than 1 supplier, then the user should right-click on the tab & make a copy of that sheet, and then start filling it out w/ the unique information, including selecting a different supplier, which, in turn, will rename that sheet accordingly.

Right now, when I right-click & select "copy", it does create a 2nd sheet w/ the same name as the 1st sheet, but w/ the (2) added to it - which I'm ok with, as long as the user can overwrite that new name by selecting a different supplier from the drop-down.... The issue is that it pops up the "Cannot rename a sheet to the same name as another sheet" error, to which there are 3 options: End, Debug, and Help.

If I click "End" then the error goes away, but I guess it kicks itself OUT of the macro, because when I choose a different supplier for the 2nd sheet, the sheet does not rename (and for that matter, Cell B22 doesn't update either). I can select a new supplier from the drop-down, but the rest of the updates do NOT occur.

Is this something you can replicate on your end?

And I guess I just want it to NOT pop-up the "Error" message, and to allow the updates / refreshes to occur on the new sheet.

Thank you!

I can replicate what you described.

Does the combobox text (selected supplier) always go in cell B22 ?

Start out with fresh with just the one worksheet and just this code for the Combobox1_Change.

Please Login or Register  to view this content.

Originally Posted by rbrookov

oh wow, thanks!! So the 1st time the user changes the 1st sheet, it pops up the msg box... is it supposed to do that?

And I think it's popping up message boxes for the number of sheets there are - for instance, if there are 3 sheets, then by the time you've made 2 copies, it goes through the loop 2 more times & pops up 3 msg boxes, each w/ the unique name - is that the intent? If so, then that's fine, and I appreciate the help you've give me thus far - it's MUCH better than what I had

It should popup one of two message boxes when you change the combobox and regardless of how many sheets you've already copied.

If the new sheet name was successful, Msgbox 1 asks if you want to make another copy.

If the new sheet name is already used, Msgbox 2 just notifies you of that and the sheet is not renamed. This is in case you accidentally chose the wrong supplier.

You're welcome. It's yours to change now. I'm sure you'll want to at least change the messages in the message boxes.