Worksheet Name vs. Worksheet Codename

[kapvg]

Hi,

I am facing a problem and am unable to figure out the reason after loads of searching...

I have an ActiveX CommandButton on Sheet2 of by workbook that I want to modify (i.e. change font etc.) via VBA code. Sheet2 is named "All Transactions" and I want to make my code work without any modifications even if the sheet is renamed to something else.

The below code works:

With ThisWorkbook.Sheets("All Transactions").CommandButton1
    .ForeColor = colorBlue
    .BackColor = colorYellow
    .Font.Name = "Calibri"
    .Font.Size = 12
    .Caption = "Click to update data in" & vbNewLine & "Column K"
End With

...but the below code does not

Dim sht2 As Worksheet
Set sht2 = ThisWorkbook.Sheets(2)
With sht2.CommandButton1
    .ForeColor = colorBlue
    .BackColor = colorYellow
    .Font.Name = "Calibri"
    .Font.Size = 12
    .Caption = "Click to update data in" & vbNewLine & "Column K"
End With

Any suggestions on why the 2nd piece of code does not work or what I can do to fix my problem?

thanks,
~kg

 

[Fluff]

If you're trying to use the codename it should be like

With Sheet2.CommandButton1

 

[kapvg]

If you're trying to use the codename it should be like

With Sheet2.CommandButton1

Thanks a ton!!! this works.

Amazingly, I am able to use the variable 'sht2' for all other calculations etc. in my code (in place of ThisWorkbook.Sheets(2)) but only with the CommandButton it does not seem to work.

 

[Fluff]

This

[COLOR=#333333]ThisWorkbook.Sheets(2)[/COLOR]

is not referring to the codename, it's referring to the sheet index (in this case the 2nd sheet from the left) & is IMO an even worse way of referring to a sheet than the sheet name.

 

[kapvg]

This

[COLOR=#333333]ThisWorkbook.Sheets(2)[/COLOR]

is not referring to the codename, it's referring to the sheet index (in this case the 2nd sheet from the left) & is IMO an even worse way of referring to a sheet than the sheet name.

Ah...could you please advise on how can I refer to a particular sheet in my code without worrying about changes in the name or location of the sheet?

 

[MARK858]

Have a read of the link below, it explains the different ways to reference sheets.

http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm

 

[kapvg]

Thanks 'Fluff' and 'MARK858'.

I have modified my code as below to assign the correct worksheets to variables (sht2 and sht3) based on the Worksheet codenames as below:

Dim temp2 As Integer
For temp2 = 1 To ThisWorkbook.Sheets.Count Step 1[INDENT]If ThisWorkbook.Sheets(temp2).CodeName = "Sheet2" Then
[/INDENT]
[INDENT=2]Set sht2 = ThisWorkbook.Sheets(temp2)[/INDENT]
[INDENT]End If
If ThisWorkbook.Sheets(temp2).CodeName = "Sheet3" Then
[/INDENT]
[INDENT=2]Set sht3 = ThisWorkbook.Sheets(temp2)[/INDENT]
[INDENT]End If[/INDENT]
Next temp2

but now I am back facing my original problem of trying to access the CommandButton properties on Sheet2 without having to actually use Sheet2.CommandButton1. Is there any way out?

thanks,
~kg

 

[MARK858]

What exactly is your issue with using the sheets codename i.e. Sheet2.CommandButton1 ?

 

[Fluff]

Not sure why you feel the need to assign variables.
Its easier to just use something along the lines of

Sheet3.CommandButton1
Sheet3.Range("A1").CurrentRegion

 

[kapvg]

My bad.
All the time while wanting to use the CodeName I forgot that Sheet2 is actually the CodeName for that very worksheet !!!! ...duh