XL2002: Button Labels & Sheets...

[Guest]

I have a 'menu sheet' in my workbook that contains several buttons which do
no more than select another sheet in the workbook. I am having to write a 1
line macro that selects the correct sheet for each button. The button labels
are the same as the sheet Tab names.

Is there a way that I can create 1 macro for all buttons that uses the
button label as a variable, and selects the relevant sheet?

I've been trying, but struggle with addressing the button label.

Thanks in advance

Trevor

 

[Guest]

Assuming you got your button from the Forms Toolbar then this code should do
it for you... Just Change the Sheets("Sheet1") to Sheets("???") where ??? is
the tab name of the sheet with the buttons on it...

Sub GoToSheet()
Sheets(Sheets("Sheet1").Buttons(Application.Caller).Caption).Select
End Sub

 

[Guest]

Excellent, thanks Jim

Trevor

Assuming you got your button from the Forms Toolbar then this code should do
it for you... Just Change the Sheets("Sheet1") to Sheets("???") where ??? is
the tab name of the sheet with the buttons on it...

Sub GoToSheet()
Sheets(Sheets("Sheet1").Buttons(Application.Caller).Caption).Select
End Sub