XL2002: Button Labels & Sheets...
 

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

XL2002: Button Labels & Sheets...
 

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
--
HTH...

Jim Thomlinson


"Trevor Williams" wrote:

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

XL2002: Button Labels & Sheets...
 

Excellent, thanks Jim

Trevor

"Jim Thomlinson" wrote:

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
--
HTH...

Jim Thomlinson


"Trevor Williams" wrote:

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