VBA Worksheet Tab Rename Macro Causing Excel Infinite Loop

[rbrookov]

Howdy,

So, I found the following code posted here by member VOG, in the following thread:

http://www.mrexcel.com/forum/excel-...ically-when-updating-a1-contains-formula.html

Private Sub Worksheet_Calculate()
On Error Resume Next
Me.Name = Range("A1").Value
On Error GoTo 0
End Sub

The 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 code that is the issue, because when I have the code commented out, I can make a copy of the Worksheet.

My "A1" in the code is a different cell, 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 (because it's listed as "Private" - I don't know how to run it in debug mode - my ignorance, I know).

Anyway, any & all help would be much appreciated.

Thanks,
Rob
Excel 2007
Windows 7

 

[rbrookov]

wow, that doesn't look so great.. the link for the other utility you provided was down, so this is all I had to work with. Anyway, just let me know if you are confused about what my spreadsheet does & I'll try to explain.

Thank you for all your assistance thus far,
Rob

 

[JoeMo]

wow, that doesn't look so great.. the link for the other utility you provided was down, so this is all I had to work with. Anyway, just let me know if you are confused about what my spreadsheet does & I'll try to explain.

Thank you for all your assistance thus far,
Rob

I'm unable to reproduce the problem you describe or to see why the worksheet_calculate macro would cause that problem. When you copy the sheet with that event code in it to the same workbook, the copy will contain the macro and the same value that was in cell S47 that you want to use for the sheet name. Excel will not permit two sheets in the same workbook with the same name, so when a calculation event occurs, Excel simply repeats the name in S47 followed by " (2)" on the sheet's tab. There is no error nor any infinite loop that I can observe.

 

[rbrookov]

I'm unable to reproduce the problem you describe or to see why the worksheet_calculate macro would cause that problem. When you copy the sheet with that event code in it to the same workbook, the copy will contain the macro and the same value that was in cell S47 that you want to use for the sheet name. Excel will not permit two sheets in the same workbook with the same name, so when a calculation event occurs, Excel simply repeats the name in S47 followed by " (2)" on the sheet's tab. There is no error nor any infinite loop that I can observe.

OK, thank you for checking. Is there any way for me to implement some code which forces the copied sheet to be renamed some generic name (like say, "next_sheet") upon copying - then, when the user chooses the supplier, it will then run the macro to rename it w/ the supplier name?

Thanks,
Rob

 

[JoeMo]

OK, thank you for checking. Is there any way for me to implement some code which forces the copied sheet to be renamed some generic name (like say, "next_sheet") upon copying - then, when the user chooses the supplier, it will then run the macro to rename it w/ the supplier name?

Thanks,
Rob

Not that I know of if the very act of copying causes an infinite loop. In my tests the copied sheet was named with the original name followed by " (2)" and when I changed the cell holding the sheet name - the name then was changed by the worksheet_calculate event code.