VBA Worksheet Tab Rename Macro Causing Excel Infinite Loop

rbrookov

New Member
Joined
May 7, 2013
Messages
16
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
I'm not convinced that the code you posted is causing the problem you describe. What happens if you set Calculation to Manual and then copy the sheet? What other event code do you have in the workbook that might be the cause?
 
Upvote 0
Hi JoeMo - thanks for responding.

I'm not sure what you mean by "set Calculation to Manual". The macro that I pasted above is the most recent thing I added. Any time prior to adding the macro, I could copy & paste a new tab. Also, as I said, if I comment out the newly added macro, I can still copy & paste a new tab.

Thus, the new macro has to either be the issue, or it is interfering / triggering something that is the issue.

I do have other macros & calculations going on in the Spreadsheet. For instance, when the user first opens the spreadsheet, I have a macro which gets his network username & then pastes it into a cell. Also upon opening, I have another macro which gets the date & pastes it into another cell. Both of those are called via the "Workbook_Open()" command.

I also have some Vlookup calculations going on, based on certain check-boxes the user checks.

The "Worksheet Tab Rename" macro (the one which is causing / triggering the infinite loop) is in the Worksheet object, along with a ComboBox Change macro. Actually, that combo box is the Drop-Down List that the user uses to select a choice. That choice gets copied into a formula in another cell (Let's say it's Cell A1) which I use to concatenate the choice + the date (the date being calculated upon 1st opening the workbook).

So, once the Combo-Box / Drop-Down List choice is selected, A1 displays that choice PLUS the date. I then want that string to be copied to the Worksheet Tab via the "Worksheet Tab Rename" macro.

I'm wondering if there is some error check I can add into the "Worksheet Tab Rename" macro? Also, I wonder if the "Workbook_open" calculations are the issue?

Thanks in advance,
Rob
 
Upvote 0
To set Calculation to Manual:eek:n the Ribbon, Formula>Calculation>Calculation Options>Manual
Once you've done that, try to copy the sheet. Since you are using a worksheet_calculate event, that event should not be triggered by copying the sheet.

I've tested the code you posted on a simple workbook and get no errors on copying the sheet the code is in. Of course when the sheet is first copied, cell A1 still has the original sheet's name and Excel will not permit assigning the same name to the copy.
 
Upvote 0
ok, if I set the calculation to manual, I can copy the sheet and there is no infinite loop. Obviously though, the macro doesn't run, as it is set to "manual".

Also, interesting that once I have the sheet copied, I then switch back to "automatic" calculation, and the tab names turn to gibberish (what looks like "wingding" fonts).
 
Last edited:
Upvote 0
P.S. Is there a way to attach the Excel spreadsheet, or do I have to copy & paste the macro(s) inline into the thread window?
 
Upvote 0
P.S. Is there a way to attach the Excel spreadsheet, or do I have to copy & paste the macro(s) inline into the thread window?
You can show the sheet layout using one of these free tools:
Download
http://www.mrexcel.com/forum/2545970-post2.html

You can copy and paste your code directly to your browser then use the # icon in the Quick Reply tool bar to surround the code with code tags.

You should post all the code you are using.
 
Upvote 0
SUPPLIER_DATE

*ABCDEFGHI
1XXX*QUOTE NUMBER 6QUOTE DATE 45/6/2014
2XXX**EXPIRY DATE 412/31/2014
3XXX
4PART NUMBER 4*SALES REP 4*
5*
6DESCRIPTION 4*
7
8*
9NOTES
(OPTIONAL) 4
XXX
10
11
12
13
14*
15SUPPLIER 4*XXXNEED MATERIALS? 4**
16*
17PROCESSWEEKSCAL DAYSBUS DAYSMATERIALS 6
18*CUT***PART #QTYXXX
19XXXWELD***
20*XXX****1$0.00
21*XXXXXX**XXX1XXX
22*SUPPLIER0XXX0XXXXXX$0.00
23*FASTEN**XXX*XXX$0.00
24XXXSEW****1$0.00
25*ENGRAVEXXXXXX**1$0.00
26*HAMMER*XXX**XXXXXX
27XXXINSPECT0.2XXXXXX*XXXXXX
28XXXTOTAL TIME 40.21.4XXXXXX$0.00
29*
30QTY BREAKS 4XXX2-45-910-1920-4950-99100+
31VENDOR COSTXXX$0.00 $0.00 $0.00 XXX$0.00 $0.00
32TOT MATERIALXXXXXXXXX$0.00 XXX$0.00 $0.00
33FINAL COST$0.00 $0.00 $0.00 $0.00 $0.00 XXX$0.00

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:22px;"><col style="width:143px;"><col style="width:71px;"><col style="width:71px;"><col style="width:71px;"><col style="width:71px;"><col style="width:71px;"><col style="width:71px;"><col style="width:71px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
I2=DATE(YEAR(I1),13,0)
C18=IF($W5,VLOOKUP($B18,ROUTE_INFO,2,FALSE),"")
D18=IF($W5,VLOOKUP($B18,ROUTE_INFO,3,FALSE),"")
E18=IF($W5,VLOOKUP($B18,ROUTE_INFO,4,FALSE),"")
C19=IF($W6,VLOOKUP($B19,ROUTE_INFO,2,FALSE),"")
D19=IF($W6,VLOOKUP($B19,ROUTE_INFO,3,FALSE),"")
E19=IF($W6,VLOOKUP($B19,ROUTE_INFO,4,FALSE),"")
C20=IF($W7,VLOOKUP($B20,ROUTE_INFO,2,FALSE),"")
D20=IF($W7,VLOOKUP($B20,ROUTE_INFO,3,FALSE),"")
E20=IF($W7,VLOOKUP($B20,ROUTE_INFO,4,FALSE),"")
C21=IF($W8,VLOOKUP($B21,ROUTE_INFO,2,FALSE),"")
D21=IF($W8,VLOOKUP($B21,ROUTE_INFO,3,FALSE),"")
E21=IF($W8,VLOOKUP($B21,ROUTE_INFO,4,FALSE),"")
C22=IF(AND($W$9,$B$22<>"SUPPLIER"),VLOOKUP($B22,VENDOR_TIMES,2,FALSE),VLOOKUP($B22,ROUTE_INFO,2,FALSE))
D22=IF(AND($W$9,$B$22<>"SUPPLIER"),VLOOKUP($B22,VENDOR_TIMES,3,FALSE),VLOOKUP($B22,ROUTE_INFO,3,FALSE))
E22=IF(AND($W$9,$B$22<>"SUPPLIER"),VLOOKUP($B22,VENDOR_TIMES,4,FALSE),VLOOKUP($B22,ROUTE_INFO,4,FALSE))
C23=IF($W10,VLOOKUP($B23,ROUTE_INFO,2,FALSE),"")
D23=IF($W10,VLOOKUP($B23,ROUTE_INFO,3,FALSE),"")
E23=IF($W10,VLOOKUP($B23,ROUTE_INFO,4,FALSE),"")
C24=IF($W11,VLOOKUP($B24,ROUTE_INFO,2,FALSE),"")
D24=IF($W11,VLOOKUP($B24,ROUTE_INFO,3,FALSE),"")
E24=IF($W11,VLOOKUP($B24,ROUTE_INFO,4,FALSE),"")
C25=IF($W12,VLOOKUP($B25,ROUTE_INFO,2,FALSE),"")
D25=IF($W12,VLOOKUP($B25,ROUTE_INFO,3,FALSE),"")
E25=IF($W12,VLOOKUP($B25,ROUTE_INFO,4,FALSE),"")
C26=IF($W13,VLOOKUP($B26,ROUTE_INFO,2,FALSE),"")
D26=IF($W13,VLOOKUP($B26,ROUTE_INFO,3,FALSE),"")
E26=IF($W13,VLOOKUP($B26,ROUTE_INFO,4,FALSE),"")
C27=IF($W14,VLOOKUP($B27,ROUTE_INFO,2,FALSE),"")
D27=IF($W14,VLOOKUP($B27,ROUTE_INFO,3,FALSE),"")
E27=IF($W14,VLOOKUP($B27,ROUTE_INFO,4,FALSE),"")
C28=SUM(C18:C27)
D28=SUM(D18:D27)
E28=SUM(E18:E27)
I28=SUMPRODUCT(H20:H27,I20:I27)
C32=$I$28
D32=$I$28
E32=$I$28
F32=$I$28
G32=$I$28
H32=$I$28
I32=$I$28
C33=SUM(C31:C32)
D33=SUM(D31:D32)
E33=SUM(E31:E32)
F33=SUM(F31:F32)
G33=SUM(G31:G32)
H33=SUM(H31:H32)
I33=SUM(I31:I32)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Last edited:
Upvote 0
SUPPLIER_DATE

*STUVW
4TASK DESCRIPTIONT (WK)T (CD)T (BD)RT CHK
5CUT175FALSE
6WELD0.42.82FALSE
7POLISH0.64.23FALSE
8GLUE0.21.41FALSE
9SUPPLIER000TRUE
10FASTEN0.85.64FALSE
11SEW0.42.82FALSE
12ENGRAVE0.64.23FALSE
13HAMMER0.21.41FALSE
14INSPECT0.21.41TRUE
15*****
16*****
17*****
18SUPPLIERT (WK)T (CD)T (BD)*
19ALPHA CO175*
20BETA CO21410*
21GAMMA CO32115*
22DELTA CO42820*
23EPSILON CO53525*
24ZETA CO64230*
25ETA CO74935*
26THETA CO85640*
27IOTA CO96345*
28KAPPA CO107050*
29LAMBDA CO117755*
30MU CO128460*
31NU CO139165*
32XI CO149870*
33*****
34*****
35U/NINITCOMP**
36USER1JONFALSE**
37USER2BILL***
38USER3FRED***
39USER4HARRY***
40USER5ROB***
41USER6SCOTT***
42USER7MARK***
43*****
44*****
45*****
46SHT_NAME****
47SUPPLIER_14****

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:143px;"><col style="width:48px;"><col style="width:49px;"><col style="width:44px;"><col style="width:54px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
T5=V5/5
U5=T5*7
T6=V6/5
U6=T6*7
T7=V7/5
U7=T7*7
T8=V8/5
U8=T8*7
T9=V9/5
U9=T9*7
T10=V10/5
U10=T10*7
T11=V11/5
U11=T11*7
T12=V12/5
U12=T12*7
T13=V13/5
U13=T13*7
T14=V14/5
U14=T14*7
U19=T19*7
V19=T19*5
U20=T20*7
V20=T20*5
U21=T21*7
V21=T21*5
U22=T22*7
V22=T22*5
U23=T23*7
V23=T23*5
U24=T24*7
V24=T24*5
U25=T25*7
V25=T25*5
U26=T26*7
V26=T26*5
U27=T27*7
V27=T27*5
U28=T28*7
V28=T28*5
U29=T29*7
V29=T29*5
U30=T30*7
V30=T30*5
U31=T31*7
V31=T31*5
U32=T32*7
V32=T32*5
S47=CONCATENATE(B22,"_",TEXT(I1,"yy"))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
This is in Sheet 1:
Private Sub CheckBox1_Click()


End Sub


Private Sub ComboBox1_Change()
ActiveSheet.Range("c15").End(xlDown).Offset(5, -1).Value = ComboBox1.Text
End Sub


Private Sub ListBox1_Click()


End Sub


Private Sub ListBox2_Click()


End Sub


Private Sub OptionButton1_Click()


End Sub


Private Sub OptionButton2_Click()


End Sub



''!!!!! THIS MACRO WORKS BUT CREATES INFINITE LOOP ONCE TAB COPIED
Private Sub Worksheet_Calculate()
On Error Resume Next
Me.Name = Range("S47").Value
DoEvents
On Error GoTo 0
End Sub
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

This is in This Workbook:

Private Sub Workbook_Open()
'If Range("C1") = "" Then
' Call UserName
'End If


If Range("i1") = "" Then
'MsgBox Date
Range("i1").Value = Date
End If


End Sub

'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
This is in Module 1:

Function GetActiveSheet() As String
' Returns the name of the worksheet from which the function is called
GetActiveSheet = ActiveSheet.Name
End Function

'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
This is in Module 2:

'Sub UserName()
'Dim UN As String
'Dim UN2 As String
' UN = Environ("username")
' 'MsgBox UN
' UN2 = WorksheetFunction.VLookup(UN, Range("UNAME"), 2, False)
' 'MsgBox UN2
' Sheets(GetActiveSheet).Range("C1:C2").Value = UN2
' End Sub
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

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
Back
Top