VBA Worksheet Tab Rename Macro Causing Excel Infinite Loop

R

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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
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:on 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
rbrookov said:
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?
Click to expand...
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
XXX*
CUT***
WELD***
XXX***
XXXXXX**
FASTEN**XXX
SEW***
ENGRAVEXXXXXX*
HAMMER*XXX*
XXX

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="colspan: 2, align: right"]XXX[/TD]
[TD="bgcolor: #00ccff"]*[/TD]
[TD="colspan: 3, align: center"]QUOTE NUMBER 6[/TD]
[TD="colspan: 2, align: right"]QUOTE DATE 4[/TD]
[TD="bgcolor: #00ccff, align: center"]5/6/2014[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="colspan: 2, align: right"]XXX[/TD]
[TD="bgcolor: #00ccff"]*[/TD]
[TD="bgcolor: #ffff00, colspan: 3"]*[/TD]
[TD="colspan: 2, align: right"]EXPIRY DATE 4[/TD]
[TD="bgcolor: #00ccff, align: center"]12/31/2014[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="colspan: 9"]XXX[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="colspan: 2, align: right"]PART NUMBER 4[/TD]
[TD="bgcolor: #ffff00, colspan: 2"]*[/TD]
[TD="colspan: 2, align: right"]SALES REP 4[/TD]
[TD="bgcolor: #ffff00, colspan: 3"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="colspan: 9"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="colspan: 2, align: right"]DESCRIPTION 4[/TD]
[TD="bgcolor: #ffff00, colspan: 7"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="colspan: 9"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="colspan: 2, align: center"]NOTES
(OPTIONAL) 4[/TD]
[TD="bgcolor: #ffff00, colspan: 7"]XXX[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="colspan: 9"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="colspan: 2, align: right"]SUPPLIER 4[/TD]
[TD="bgcolor: #ffff00, colspan: 2"]*[/TD]

[TD="colspan: 2"]NEED MATERIALS? 4[/TD]
[TD="bgcolor: #ffff00"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="colspan: 9"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="colspan: 2, align: center"]PROCESS[/TD]
[TD="align: center"]WEEKS[/TD]
[TD="align: center"]CAL DAYS[/TD]
[TD="align: center"]BUS DAYS[/TD]
[TD="colspan: 4, align: center"]MATERIALS 6[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="bgcolor: #ffff00"]*[/TD]

[TD="colspan: 2, align: center"]PART #[/TD]
[TD="align: center"]QTY[/TD]
[TD="align: center"]XXX[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="bgcolor: #ffff00"]XXX[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="bgcolor: #ffff00"]*[/TD]

[TD="colspan: 2"]*[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$0.00 [/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="bgcolor: #ffff00"]*[/TD]

[TD="colspan: 2"]XXX[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]XXX[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD="bgcolor: #ffff00"]*[/TD]
[TD="bgcolor: #00ccff"]SUPPLIER[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]0[/TD]
[TD="colspan: 2"]XXX[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]$0.00 [/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]
[TD="bgcolor: #ffff00"]*[/TD]

[TD="colspan: 2"]*[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]$0.00 [/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="bgcolor: #ffff00"]XXX[/TD]

[TD="colspan: 2"]*[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$0.00 [/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]
[TD="bgcolor: #ffff00"]*[/TD]

[TD="colspan: 2"]*[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$0.00 [/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]
[TD="bgcolor: #ffff00"]*[/TD]

[TD="colspan: 2"]*[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]XXX[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]
[TD="bgcolor: #ffff00"]XXX[/TD]
[TD="bgcolor: #00ccff"]INSPECT[/TD]
[TD="align: center"]0.2[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]XXX[/TD]
[TD="colspan: 2"]*[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]XXX[/TD]

[TD="bgcolor: #cacaca, align: center"]28[/TD]

[TD="align: right"]TOTAL TIME 4[/TD]
[TD="bgcolor: #00ccff, align: center"]0.2[/TD]
[TD="bgcolor: #00ccff, align: center"]1.4[/TD]
[TD="bgcolor: #00ccff, align: center"]XXX[/TD]
[TD="colspan: 3, align: right"]XXX[/TD]
[TD="align: center"]$0.00[/TD]

[TD="bgcolor: #cacaca, align: center"]29[/TD]
[TD="colspan: 9"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]30[/TD]
[TD="colspan: 2, align: right"]QTY BREAKS 4[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]2-4[/TD]
[TD="align: center"]5-9[/TD]
[TD="align: center"]10-19[/TD]
[TD="align: center"]20-49[/TD]
[TD="align: center"]50-99[/TD]
[TD="align: center"]100+[/TD]

[TD="bgcolor: #cacaca, align: center"]31[/TD]
[TD="colspan: 2"]VENDOR COST[/TD]
[TD="bgcolor: #ffff00, align: center"]XXX[/TD]
[TD="bgcolor: #ffff00, align: center"]$0.00 [/TD]
[TD="bgcolor: #ffff00, align: center"]$0.00 [/TD]
[TD="bgcolor: #ffff00, align: center"]$0.00 [/TD]
[TD="bgcolor: #ffff00, align: center"]XXX[/TD]
[TD="bgcolor: #ffff00, align: center"]$0.00 [/TD]
[TD="bgcolor: #ffff00, align: center"]$0.00 [/TD]

[TD="bgcolor: #cacaca, align: center"]32[/TD]
[TD="colspan: 2"]TOT MATERIAL[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]$0.00 [/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]$0.00 [/TD]
[TD="align: center"]$0.00 [/TD]

[TD="bgcolor: #cacaca, align: center"]33[/TD]
[TD="colspan: 2"]FINAL COST[/TD]
[TD="bgcolor: #008000, align: center"]$0.00 [/TD]
[TD="bgcolor: #008000, align: center"]$0.00 [/TD]
[TD="bgcolor: #008000, align: center"]$0.00 [/TD]
[TD="bgcolor: #008000, align: center"]$0.00 [/TD]
[TD="bgcolor: #008000, align: center"]$0.00 [/TD]
[TD="bgcolor: #008000, align: center"]XXX[/TD]
[TD="bgcolor: #008000, align: center"]$0.00 [/TD]

</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
TASK DESCRIPTION
CUT
WELD
POLISH
GLUE
SUPPLIER
FASTEN
SEW
ENGRAVE
HAMMER
INSPECT
*****
*****
*****
SUPPLIER*
ALPHA CO*
BETA CO*
GAMMA CO*
DELTA CO*
EPSILON CO*
ZETA CO*
ETA CO*
THETA CO*
IOTA CO*
KAPPA CO*
LAMBDA CO*
MU CO*
NU CO*
XI CO*
*****
*****
**
**
***
***
***
***
***
***
*****
*****
*****
****
****

<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>
[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: center"]T (WK)[/TD]
[TD="align: center"]T (CD)[/TD]
[TD="align: center"]T (BD)[/TD]
[TD="align: center"]RT CHK[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: center"]0.4[/TD]
[TD="align: center"]2.8[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: center"]0.6[/TD]
[TD="align: center"]4.2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: center"]0.2[/TD]
[TD="align: center"]1.4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]TRUE[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: center"]0.8[/TD]
[TD="align: center"]5.6[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: center"]0.4[/TD]
[TD="align: center"]2.8[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: center"]0.6[/TD]
[TD="align: center"]4.2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: center"]0.2[/TD]
[TD="align: center"]1.4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: center"]0.2[/TD]
[TD="align: center"]1.4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]TRUE[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]

[TD="align: center"]T (WK)[/TD]
[TD="align: center"]T (CD)[/TD]
[TD="align: center"]T (BD)[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]20[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]25[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]42[/TD]
[TD="align: center"]30[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]49[/TD]
[TD="align: center"]35[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]56[/TD]
[TD="align: center"]40[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]63[/TD]
[TD="align: center"]45[/TD]

[TD="bgcolor: #cacaca, align: center"]28[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]70[/TD]
[TD="align: center"]50[/TD]

[TD="bgcolor: #cacaca, align: center"]29[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]77[/TD]
[TD="align: center"]55[/TD]

[TD="bgcolor: #cacaca, align: center"]30[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]84[/TD]
[TD="align: center"]60[/TD]

[TD="bgcolor: #cacaca, align: center"]31[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]91[/TD]
[TD="align: center"]65[/TD]

[TD="bgcolor: #cacaca, align: center"]32[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]98[/TD]
[TD="align: center"]70[/TD]

[TD="bgcolor: #cacaca, align: center"]33[/TD]

[TD="bgcolor: #cacaca, align: center"]34[/TD]

[TD="bgcolor: #cacaca, align: center"]35[/TD]
[TD="align: center"]U/N[/TD]
[TD="align: center"]INIT[/TD]
[TD="align: center"]COMP[/TD]

[TD="bgcolor: #cacaca, align: center"]36[/TD]
[TD="align: center"]USER1[/TD]
[TD="align: center"]JON[/TD]
[TD="align: center"]FALSE[/TD]

[TD="bgcolor: #cacaca, align: center"]37[/TD]
[TD="align: center"]USER2[/TD]
[TD="align: center"]BILL[/TD]

[TD="bgcolor: #cacaca, align: center"]38[/TD]
[TD="align: center"]USER3[/TD]
[TD="align: center"]FRED[/TD]

[TD="bgcolor: #cacaca, align: center"]39[/TD]
[TD="align: center"]USER4[/TD]
[TD="align: center"]HARRY[/TD]

[TD="bgcolor: #cacaca, align: center"]40[/TD]
[TD="align: center"]USER5[/TD]
[TD="align: center"]ROB[/TD]

[TD="bgcolor: #cacaca, align: center"]41[/TD]
[TD="align: center"]USER6[/TD]
[TD="align: center"]SCOTT[/TD]

[TD="bgcolor: #cacaca, align: center"]42[/TD]
[TD="align: center"]USER7[/TD]
[TD="align: center"]MARK[/TD]

[TD="bgcolor: #cacaca, align: center"]43[/TD]

[TD="bgcolor: #cacaca, align: center"]44[/TD]

[TD="bgcolor: #cacaca, align: center"]45[/TD]

[TD="bgcolor: #cacaca, align: center"]46[/TD]
[TD="align: center"]SHT_NAME[/TD]

[TD="bgcolor: #cacaca, align: center"]47[/TD]
[TD="align: center"]SUPPLIER_14[/TD]

</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
You must log in or register to reply here.

Similar threads

D
  • Question Question
VBA Download URL and rename file
Replies
6
Views
213
Jeffrey Mahoney
Jeffrey Mahoney
M
  • Solved
Auto increment by 1 when rename excel any tab if name exist
Replies
1
Views
193
jkpieterse
jkpieterse
O
  • Solved
Copy a sheet which is a template based on a drop down list and rename each copied tab with each value in the drop down list
2 3
Replies
21
Views
1K
dmt32
D
A
  • Question Question
Rename sheets by cell value in date format
Replies
1
Views
92
Kaper
Kaper
G
  • Question Question
Jump to a TAB from a Drop-Down Menu
2
Replies
12
Views
386
LongToast
L

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

Share this page

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