Detrmine which command button was clicked

[Jamal]

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

 

[Bernie Deitrick]

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).Activate
End Sub

And assign that macro to all your commandbar buttons.

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

assuming the caption of the button is like Sheet1

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

 

[Tom Ogilvy]

Shouldn't have those quotes in there - apologies

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

 

[Jamal]

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