Access to user-created menu

[Otto Moehrbach]

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

 

[Bob Phillips]

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)

 

[Chip Pearson]

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]

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

 

[Otto Moehrbach]

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]

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)

 

[Bob Phillips]

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]

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

 

[Guest]

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

 

[Bob Phillips]

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)