Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access to user-created menu
Excel XP & Win XP
I have a user created menu that I built with John Walkenbach's easy menu maker. The menu has about 30 items. Of course, the menu is available to any open workbook besides the also-open intended workbook. I know how to place code in each macro for each menu item to check the active workbook name. My question is this: Can I prevent access or pop up a MsgBox (and cancel) when the primary menu item is accessed with the wrong workbook active, rather than do the same thing for each of the 30 menu item macros? Thanks for your time. Otto |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access to user-created menu
You could always add the menu in the Workbook_Activate event rather than
Workbook_Open, and remove it in the Workbook_Deactivate. Other workbooks won't even see it in that case. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Otto Moehrbach" wrote in message ... Excel XP & Win XP I have a user created menu that I built with John Walkenbach's easy menu maker. The menu has about 30 items. Of course, the menu is available to any open workbook besides the also-open intended workbook. I know how to place code in each macro for each menu item to check the active workbook name. My question is this: Can I prevent access or pop up a MsgBox (and cancel) when the primary menu item is accessed with the wrong workbook active, rather than do the same thing for each of the 30 menu item macros? Thanks for your time. Otto |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access to user-created menu
Otto,
Do the menu items have a Tag property set? If so, you can use code in ThisWorkbook like the following, where C_TAG is the tag associated with either all the menu items or the top-level menu item. Private Sub Workbook_Activate() ''''''''''''''''''''''''''''''''''''' ' Make the controls visible for this ' workbook. ''''''''''''''''''''''''''''''''''''' Dim Ctrl As Office.CommandBarControl On Error Resume Next For Each Ctrl In Application.CommandBars.FindControls(Tag:=C_TAG) Ctrl.Visible = True ' OR Ctrl.Enabled = True Next Ctrl End Sub Private Sub Workbook_Deactivate() ''''''''''''''''''''''''''''''''''''''' ' Hide the controls for other workboks. ''''''''''''''''''''''''''''''''''''''' Dim Ctrl As Office.CommandBarControl On Error Resume Next For Each Ctrl In Application.CommandBars.FindControls(Tag:=C_TAG) Ctrl.Visible = False ' OR Ctrl.Enabled = False Next Ctrl End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Otto Moehrbach" wrote in message ... Excel XP & Win XP I have a user created menu that I built with John Walkenbach's easy menu maker. The menu has about 30 items. Of course, the menu is available to any open workbook besides the also-open intended workbook. I know how to place code in each macro for each menu item to check the active workbook name. My question is this: Can I prevent access or pop up a MsgBox (and cancel) when the primary menu item is accessed with the wrong workbook active, rather than do the same thing for each of the 30 menu item macros? Thanks for your time. Otto |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access to user-created menu
Bob
That never occurred to me. That's a good idea. Can I ask you another related question? In the macro call for a menu item, I want to pass a string value. Right now, the macro contains only a MsgBox telling me the passed string. The problem: The MsgBox fires twice (click OK and the MsgBox appears again). If I do not pass a value and change the MsgBox to simply "Hello", the MsgBox fires only once. Is there a rationale for this? Thanks for your time. Otto "Bob Phillips" wrote in message ... You could always add the menu in the Workbook_Activate event rather than Workbook_Open, and remove it in the Workbook_Deactivate. Other workbooks won't even see it in that case. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Otto Moehrbach" wrote in message ... Excel XP & Win XP I have a user created menu that I built with John Walkenbach's easy menu maker. The menu has about 30 items. Of course, the menu is available to any open workbook besides the also-open intended workbook. I know how to place code in each macro for each menu item to check the active workbook name. My question is this: Can I prevent access or pop up a MsgBox (and cancel) when the primary menu item is accessed with the wrong workbook active, rather than do the same thing for each of the 30 menu item macros? Thanks for your time. Otto |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access to user-created menu
Chip
Thanks for that, but I don't see a place in the MenuSheet (that goes with John's easy menu builder) for a tag, nor in the code itself. If you look at his code, perhaps you could show me how to add the tag. Thanks for your help. Otto "Chip Pearson" wrote in message ... Otto, Do the menu items have a Tag property set? If so, you can use code in ThisWorkbook like the following, where C_TAG is the tag associated with either all the menu items or the top-level menu item. Private Sub Workbook_Activate() ''''''''''''''''''''''''''''''''''''' ' Make the controls visible for this ' workbook. ''''''''''''''''''''''''''''''''''''' Dim Ctrl As Office.CommandBarControl On Error Resume Next For Each Ctrl In Application.CommandBars.FindControls(Tag:=C_TAG) Ctrl.Visible = True ' OR Ctrl.Enabled = True Next Ctrl End Sub Private Sub Workbook_Deactivate() ''''''''''''''''''''''''''''''''''''''' ' Hide the controls for other workboks. ''''''''''''''''''''''''''''''''''''''' Dim Ctrl As Office.CommandBarControl On Error Resume Next For Each Ctrl In Application.CommandBars.FindControls(Tag:=C_TAG) Ctrl.Visible = False ' OR Ctrl.Enabled = False Next Ctrl End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Otto Moehrbach" wrote in message ... Excel XP & Win XP I have a user created menu that I built with John Walkenbach's easy menu maker. The menu has about 30 items. Of course, the menu is available to any open workbook besides the also-open intended workbook. I know how to place code in each macro for each menu item to check the active workbook name. My question is this: Can I prevent access or pop up a MsgBox (and cancel) when the primary menu item is accessed with the wrong workbook active, rather than do the same thing for each of the 30 menu item macros? Thanks for your time. Otto |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access to user-created menu
Otto,
I usually use the Parameter property of a CommandBarControl to pass information to the OnAction procedure. For example, when you create the control, use something like Ctrl.Parameter = "Some Text" Ctrl.OnAction = "'" & ThisWorkbook.Name & "'!ProcName" Then in the OnAction procedure ProcName, Sub ProcName() If Not Application.CommandBars.ActionControl Is Nothing Then MsgBox Application.CommandBars.ActionControl.Parameter End If End Sub Note that the OnAction property can set the Parameter value of another control, so you can pass context-sensitive information between command bar buttons. For example, the OnAction procedure of Button1 can put information in the Parameter property of Button2, and then Button2 can run code that depends on whether Button1 has previous been clicked. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Otto Moehrbach" wrote in message ... Bob That never occurred to me. That's a good idea. Can I ask you another related question? In the macro call for a menu item, I want to pass a string value. Right now, the macro contains only a MsgBox telling me the passed string. The problem: The MsgBox fires twice (click OK and the MsgBox appears again). If I do not pass a value and change the MsgBox to simply "Hello", the MsgBox fires only once. Is there a rationale for this? Thanks for your time. Otto "Bob Phillips" wrote in message ... You could always add the menu in the Workbook_Activate event rather than Workbook_Open, and remove it in the Workbook_Deactivate. Other workbooks won't even see it in that case. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Otto Moehrbach" wrote in message ... Excel XP & Win XP I have a user created menu that I built with John Walkenbach's easy menu maker. The menu has about 30 items. Of course, the menu is available to any open workbook besides the also-open intended workbook. I know how to place code in each macro for each menu item to check the active workbook name. My question is this: Can I prevent access or pop up a MsgBox (and cancel) when the primary menu item is accessed with the wrong workbook active, rather than do the same thing for each of the 30 menu item macros? Thanks for your time. Otto |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access to user-created menu
How are you passing this value?
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Otto Moehrbach" wrote in message ... Bob That never occurred to me. That's a good idea. Can I ask you another related question? In the macro call for a menu item, I want to pass a string value. Right now, the macro contains only a MsgBox telling me the passed string. The problem: The MsgBox fires twice (click OK and the MsgBox appears again). If I do not pass a value and change the MsgBox to simply "Hello", the MsgBox fires only once. Is there a rationale for this? Thanks for your time. Otto "Bob Phillips" wrote in message ... You could always add the menu in the Workbook_Activate event rather than Workbook_Open, and remove it in the Workbook_Deactivate. Other workbooks won't even see it in that case. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Otto Moehrbach" wrote in message ... Excel XP & Win XP I have a user created menu that I built with John Walkenbach's easy menu maker. The menu has about 30 items. Of course, the menu is available to any open workbook besides the also-open intended workbook. I know how to place code in each macro for each menu item to check the active workbook name. My question is this: Can I prevent access or pop up a MsgBox (and cancel) when the primary menu item is accessed with the wrong workbook active, rather than do the same thing for each of the 30 menu item macros? Thanks for your time. Otto |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access to user-created menu
Bob
In the MenuSheet that is a part of John's easy menu creator system, there is a column for me to enter the name of the On-Action macro for each menu item. I simply wrote the macro name as: TheMacro("TheStr") Apparently, from what Chip says and what I get, this is not the way to pass a value to that macro. The macro, at this time is: Sub TheMacro(X As String) MsgBox X End Sub Thanks for your time. Otto "Bob Phillips" wrote in message ... How are you passing this value? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Otto Moehrbach" wrote in message ... Bob That never occurred to me. That's a good idea. Can I ask you another related question? In the macro call for a menu item, I want to pass a string value. Right now, the macro contains only a MsgBox telling me the passed string. The problem: The MsgBox fires twice (click OK and the MsgBox appears again). If I do not pass a value and change the MsgBox to simply "Hello", the MsgBox fires only once. Is there a rationale for this? Thanks for your time. Otto "Bob Phillips" wrote in message ... You could always add the menu in the Workbook_Activate event rather than Workbook_Open, and remove it in the Workbook_Deactivate. Other workbooks won't even see it in that case. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Otto Moehrbach" wrote in message ... Excel XP & Win XP I have a user created menu that I built with John Walkenbach's easy menu maker. The menu has about 30 items. Of course, the menu is available to any open workbook besides the also-open intended workbook. I know how to place code in each macro for each menu item to check the active workbook name. My question is this: Can I prevent access or pop up a MsgBox (and cancel) when the primary menu item is accessed with the wrong workbook active, rather than do the same thing for each of the 30 menu item macros? Thanks for your time. Otto |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access to user-created menu
Hi Otto,
While John's "Easy Menu Maker" is a good way to create menus in a table-driven format, I believe it's intent is to provide a rather simplistic approach to creating menus. You may want to consider using Rob Bovey's Commandbar Builder method outlined in Ch.8 of the book "Professional Excel Development". It handles all the commandbar/menu issues you raise here, ..and much much more. It uses 3 modules, but they're set up to just "drop-in" to any project. Sample files are on the CD included with the book. I checked to see if there's any downloads from his or Stephen Bullen's sites, but nothing is available to date. You'll just have to get the book! (which you won't regret) HTH GS |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access to user-created menu
No, it is not Otto. There is no facility to pass a parameter directly in the
OnAction call. You have to set another property, such as Parameter or tag, and test that in the called macro using Application.ActionControl, as Chip showed. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Otto Moehrbach" wrote in message ... Bob In the MenuSheet that is a part of John's easy menu creator system, there is a column for me to enter the name of the On-Action macro for each menu item. I simply wrote the macro name as: TheMacro("TheStr") Apparently, from what Chip says and what I get, this is not the way to pass a value to that macro. The macro, at this time is: Sub TheMacro(X As String) MsgBox X End Sub Thanks for your time. Otto "Bob Phillips" wrote in message ... How are you passing this value? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Otto Moehrbach" wrote in message ... Bob That never occurred to me. That's a good idea. Can I ask you another related question? In the macro call for a menu item, I want to pass a string value. Right now, the macro contains only a MsgBox telling me the passed string. The problem: The MsgBox fires twice (click OK and the MsgBox appears again). If I do not pass a value and change the MsgBox to simply "Hello", the MsgBox fires only once. Is there a rationale for this? Thanks for your time. Otto "Bob Phillips" wrote in message ... You could always add the menu in the Workbook_Activate event rather than Workbook_Open, and remove it in the Workbook_Deactivate. Other workbooks won't even see it in that case. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Otto Moehrbach" wrote in message ... Excel XP & Win XP I have a user created menu that I built with John Walkenbach's easy menu maker. The menu has about 30 items. Of course, the menu is available to any open workbook besides the also-open intended workbook. I know how to place code in each macro for each menu item to check the active workbook name. My question is this: Can I prevent access or pop up a MsgBox (and cancel) when the primary menu item is accessed with the wrong workbook active, rather than do the same thing for each of the 30 menu item macros? Thanks for your time. Otto |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add menuitems to custom created menu | Excel Programming | |||
Add list to newly created menu | Excel Discussion (Misc queries) | |||
add list to newly created menu | Excel Worksheet Functions | |||
Add items to VBA-created menu? | Excel Programming | |||
Menu item created w/ add-in removed due other add-in | Excel Programming |