Link button name to a cell

[neda5]

Hi,
I have an index sheet with buttons that I want to have such that when each
button is clicked it goes to a specific worksheet within the file and for
that I have brought a list of all sheet names in that index tab. I would like
to have the button names linked to the respective cells that contain the
names of the tabs so that if I change the tab names the buttons automtically
update.
Can anyone help me with how I link the button name (caption) to a cell?
Thanks in advance.
Neda

 

[OssieMac]

Hi Neda,

A couple of questions. Not really sure if I can help but I will have a look
at it and see what can be done.

What sort of buttons do you have? Did you create them from the button on the
Forms toolbar or from the button on the Control Box Toolbar?

How are you getting the sheet names into the Index tab? (By formula or with
a macro or are you just typing them in?)

Regards,

OssieMac

 

[neda5]

Thanks.
The buttone I have created are from the Control Box Toolbar and have a
simple macro assigned to them to tell them to jump to a specific sheet when
pressed.
I am getting a list of my tab names with the help of a macro so that when
the tab names change the list on the index tab changes too. (It is actually a
udf as follows:
Public Function TabI(TabIndex As Integer, Optional MyTime As Date) As String
TabI = Sheets(TabIndex).Name
End Function
and a formula I have in the indextab)
What I really want to do is link the button names to that list so that when
I change the name of a tab ot gets reflected in the list and the respective
button.
Furthermore, the macro I am using is the following:

Private Sub CommandButton1_Click()
Worksheets("Name of Sheet").Activate
ActiveSheet.Range("A1").Select
End Sub

Ideally I would like to have the name of the sheet to be activated at the
click of the button instead of being typed to be linked to the specific
sheet. Is that doable?

Thanks. Neda

 

[OssieMac]

Hi Needa,

The best I have been able to come up with is to use the worksheet activate
event for the sheet which has the buttons because you only want the buttons
to be updated when you change back to that sheet. Each time you select the
worksheet with the buttons, the captions get updated.

You could either update the captions directly from the sheet names or from
the cells with the names in them.

Something you need to know is that each worksheet has a code name which is
not changed when you rename the worksheet. You can see them in the project
explorer part of the VBA editor. The names are Sheet1, Sheet2 etc and the
name that you can change is the one in brackets. However, you can still refer
to the worksheets by the code name as I have done in the code below because
that name never changes. Note that the code names are not the same as
Sheets(1), Sheets(2) etc which refer to the sheets from left to right in the
workbook.

In the code I have demonstrated changing the caption by copying the cell
reference and also directly copying the worksheet given name.

Private Sub Worksheet_Activate()
With Sheet1
.Cells(6, 3) = Sheet2.Name
.CommandButton2.Caption = .Cells(6, 3)
.Cells(10, 3) = Sheet3.Name
.CommandButton3.Caption = Sheet3.Name
.Cells(14, 3) = Sheet4.Name
.CommandButton4.Caption = Sheet4.Name
.Cells(18, 3) = Sheet5.Name
.CommandButton5.Caption = Sheet5.Name
End With
End Sub

Hope it helps.

Regards,

OssieMac

 

[neda5]

Thanks for all your help.
Neda