I have a custom menu on my sheet. Each menu option's name contains the name
of the sheet I want to interact with. What I want to do is when the user
clicks the menu item I want to pass either the name of the menu item clicked
or pass an object referring to the menu button, so I can write one method
rather than 12 to control the menu. is this possible in VBA?

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

The

Application.CommandBars.ActionControl

returns a CommandBarButton object referencing the button that called the
assigned procedure. So if you have several buttons assigned to the same
procedure, this object will tell you what button called it. You can then get
the Caption of the control to see what see it needs to work with.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)'



"J Streger" wrote in message
...
I have a custom menu on my sheet. Each menu option's name contains the name
of the sheet I want to interact with. What I want to do is when the user
clicks the menu item I want to pass either the name of the menu item
clicked
or pass an object referring to the menu button, so I can write one method
rather than 12 to control the menu. is this possible in VBA?

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

Thank you Chip. Worked like a charm.

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003



"Chip Pearson" wrote:

The

Application.CommandBars.ActionControl

returns a CommandBarButton object referencing the button that called the
assigned procedure. So if you have several buttons assigned to the same
procedure, this object will tell you what button called it. You can then get
the Caption of the control to see what see it needs to work with.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)'



"J Streger" wrote in message
...
I have a custom menu on my sheet. Each menu option's name contains the name
of the sheet I want to interact with. What I want to do is when the user
clicks the menu item I want to pass either the name of the menu item
clicked
or pass an object referring to the menu button, so I can write one method
rather than 12 to control the menu. is this possible in VBA?

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

I used a loop to setup the names of each button representing the name of each
sheet in my workbook. Basically if the user clicked on the button, it would
take them to that page (very simple). I couldnt figure out how to get the
sheet name they wanted (ie. the caption of the button). I set a tag value to
the button and it worked great.

Dim pg as String
pg = CommandBars.ActionControl.Tag 'pg is the name of the sheet user wants
to switch to.

"Chip Pearson" wrote:

The

Application.CommandBars.ActionControl

returns a CommandBarButton object referencing the button that called the
assigned procedure. So if you have several buttons assigned to the same
procedure, this object will tell you what button called it. You can then get
the Caption of the control to see what see it needs to work with.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)'



"J Streger" wrote in message
...
I have a custom menu on my sheet. Each menu option's name contains the name
of the sheet I want to interact with. What I want to do is when the user
clicks the menu item I want to pass either the name of the menu item
clicked
or pass an object referring to the menu button, so I can write one method
rather than 12 to control the menu. is this possible in VBA?

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003