Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro button gets bigger when clicked | Excel Discussion (Misc queries) | |||
VBA button on Developer ribbon can not be clicked | Excel Discussion (Misc queries) | |||
Preventing a command button from being clicked twice | Excel Discussion (Misc queries) | |||
let a sub recognise the name of the button that has been clicked. | Excel Discussion (Misc queries) | |||
Button event clicked problem | Excel Programming |