Reference to Sheet Name for Dynamic Button

Microsoft Excel 2007
August 26, 2009 at 08:23:14
Specs: Windows XP
Hi,

Background Information:
I am creating a DASHBOARD sheet to be at the beginning of my workbook with multiple sheets.

In DASHBOARD I have created a chart that contains client analysis formulas that reference to the appropriate client sheet name in the same workbook by reference to Cell C8. To do this, in each statistics formula I have used the Indirect reference: =INDIRECT("'" & C8 &"'!G3")

My next step is to make a dynamic button that also uses Cell C8 as the sheet name to jump to. AKA: I want the "Go" button to jump the user exactly to the client sheet which is named in cell C8.

I have used this simple VBA to use a button to jump to a sheet name:

Private Sub Client_Click()

Sheets("Client-Template").Select

End Sub

I need help to make the sheet reference dynamically linked to the contents of Cell C8 which will have the exact worksheet name of any of the clients:
The following is my failed attempt to create a dynamic variable that depended on Cell C8
Private Sub Client_Click()
Client as String

Client = Range(C8)
Sheets("Client").Select

End Sub

Thank you for any and all of your help!


See More: Reference to Sheet Name for Dynamic Button

Report •


#1
August 26, 2009 at 08:33:36
Just fixing some syntax errors - mainly misplaced quotes:

Private Sub Client_Click()
Dim Client As String
 Client = Range("C8")
 Sheets(Client).Select
End Sub

or, simply

Private Sub Client_Click()
  Sheets(Range("C8").Value).Select
End Sub


Report •

#2
August 27, 2009 at 11:48:45
Thank you so much! I knew I had been missing something in the syntax.

I applied the second, more simple approach and it works like a dream. This is a small but major addition in functionality.

I now have a dynamic jump button, this is probably very useful to any one else with workbooks that have many sheets and use a dashboard summary to manage data.

Thanks again DerbyDad03!


Report •

Related Solutions


Ask Question