Reference to Sheet Name for Dynamic Button

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

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()


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)

End Sub

Thank you for any and all of your help!

See More: Reference to Sheet Name for Dynamic Button

Report •

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")
End Sub

or, simply

Private Sub Client_Click()
End Sub

Report •

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