Detrmine which command button was clicked
 

I have a workbook with more than twenty sheets. I
regularly add and delete sheets. I have created a custom
menu on the standard menu bar and a amcro to show all the
sheets by their name (Caption) on the menu everytime the
workbook is activated. The idea is to click the sheet name
on the menu to go to a given sheet . However, I am finding
it difficult to assign a single macro to OnAction property
which will determine which sheet was selected and then
activate that sheet.

Any help is appreciated

Thanks in anticipation

Jamal

Detrmine which command button was clicked
 

Jamal,

CommandBars.ActionControl

is the button that is pressed. You can access any of its properties,
along the lines of:

CommandBars.ActionControl.Caption

You could use it like

Sub SelectSheet()
Worksheets(CommandBars.ActionControl.Caption).Acti vate
End Sub

And assign that macro to all your commandbar buttons.

HTH,
Bernie
MS Excel MVP

"Jamal" wrote in message
...
I have a workbook with more than twenty sheets. I
regularly add and delete sheets. I have created a custom
menu on the standard menu bar and a amcro to show all the
sheets by their name (Caption) on the menu everytime the
workbook is activated. The idea is to click the sheet name
on the menu to go to a given sheet . However, I am finding
it difficult to assign a single macro to OnAction property
which will determine which sheet was selected and then
activate that sheet.

Any help is appreciated

Thanks in anticipation

Jamal

Detrmine which command button was clicked
 

assuming the caption of the button is like Sheet1

Sub SheetButtonClick()
sName = Commandbars.ActionControl.Caption
Worksheets("sName").Activate
End Sub

--
Regards,
Tom Ogilvy


"Jamal" wrote in message
...
I have a workbook with more than twenty sheets. I
regularly add and delete sheets. I have created a custom
menu on the standard menu bar and a amcro to show all the
sheets by their name (Caption) on the menu everytime the
workbook is activated. The idea is to click the sheet name
on the menu to go to a given sheet . However, I am finding
it difficult to assign a single macro to OnAction property
which will determine which sheet was selected and then
activate that sheet.

Any help is appreciated

Thanks in anticipation

Jamal

Detrmine which command button was clicked
 

Shouldn't have those quotes in there - apologies

Sub SheetButtonClick()
sName = Commandbars.ActionControl.Caption
Worksheets(sName).Activate
End Sub

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
assuming the caption of the button is like Sheet1

Sub SheetButtonClick()
sName = Commandbars.ActionControl.Caption
Worksheets("sName").Activate
End Sub

--
Regards,
Tom Ogilvy


"Jamal" wrote in message
...
I have a workbook with more than twenty sheets. I
regularly add and delete sheets. I have created a custom
menu on the standard menu bar and a amcro to show all the
sheets by their name (Caption) on the menu everytime the
workbook is activated. The idea is to click the sheet name
on the menu to go to a given sheet . However, I am finding
it difficult to assign a single macro to OnAction property
which will determine which sheet was selected and then
activate that sheet.

Any help is appreciated

Thanks in anticipation

Jamal

Detrmine which command button was clicked
 

Thank you, Bernie and Tom. Both method works magic. Much
appreciated.

-----Original Message-----
Jamal,

CommandBars.ActionControl

is the button that is pressed. You can access any of its
properties,
along the lines of:

CommandBars.ActionControl.Caption

You could use it like

Sub SelectSheet()
Worksheets(CommandBars.ActionControl.Caption).Act ivate
End Sub

And assign that macro to all your commandbar buttons.

HTH,
Bernie
MS Excel MVP

"Jamal" wrote in
message
...
I have a workbook with more than twenty sheets. I
regularly add and delete sheets. I have created a custom
menu on the standard menu bar and a amcro to show all
the
sheets by their name (Caption) on the menu everytime the
workbook is activated. The idea is to click the sheet
name
on the menu to go to a given sheet . However, I am
finding
it difficult to assign a single macro to OnAction
property
which will determine which sheet was selected and then
activate that sheet.

Any help is appreciated

Thanks in anticipation

Jamal


.