Send menu call to explicit Sub

S

Stuart

Am trying to direct a menu call to a specific Project and
Sub. The following attempt is not working:

With .Controls.Add(Type:=msoControlButton)
.Caption = "&Add a Standard Page"
.OnAction = "AddBofQStandardPage"
End With

More correctly, it is working, but is calling the sub
"AddBofQStandardPage" from a module in a different
Project.

How do I reference the sub in, say
Project1, Module1, when the the menu item is
triggered, please?

Regards.
 
T

Tom Ogilvy

.OnAction = "MyWorkbookName.xls!AddBofQStandardPage"

Assume you don't have two procedures with that name in that workbook. If
the workbook isn't open, include the path.
 
S

Stuart

Just to be sure, can one reference the module in that statement,
ie
..OnAction = "MyWorkbookName.xls! _Module1_AddBofQStandardPage"

Regards.
 
S

Stuart

Most odd.

..OnAction = "BofQ Utilities.xla!Menu_Routines.AddBofQStandardPage"
results in a the same named sub being called from a completely different
project and module....

How can this be, please?

Regards.
 
T

Tom Ogilvy

No offense intended, but naturally I remain incredulous.
How can this be, please?
Click to expand...
Some mistake or assumption on your part I would assume.

In the macro that is running (not the one you cite), put in a line of code
like

msgbox commandbars.ActionControl.OnAction

If it shows

BofQ Utilities.xla!Menu_Routines.AddBofQStandardPage

and that isn't the macro that is running, then I'm the fool.
 
S

Stuart

Thanks for the help. Here's what I've done:

In the subs named "AddBofQStandardPage" across 3 projects
there is a line:
If Not (.Column = 1 And (.Offset(-1, £Col - 1).Value = "£") And _
.Row > 1 And .Value = "Item") Then
MsgBox "You cannot insert or add a page here: _
" & ActiveCell.Address
Exit Sub
End If

I placed a breakpoint on "Exit Sub" in each of the routines. Then
choosing a suitable non-acceptable cell, I ran the menu code.
It stopped in "AddBofQStandardPage" in module "MenuRoutines"
in project "MasterPQSBofQCode1.xla".

The menu call operating is:
With .Controls.Add(Type:=msoControlButton)
.Caption = "&Add a Standard Page"
.OnAction = "BofQ Utilities.xla!Menu_Routines.AddBofQStandardPage"
End With
and it is being called from "BofQ Utilities.xla".

I spent several hours before my original post trying to work out
what was going on (including using
BofQ Utilities.xla!.AddBofQStandardPage) without success.
(Didn't know how to include a reference to the module)

I set a new breakpoint, then added
"msgbox commandbars.ActionControl.OnAction"
into each of the named subs, just before "Exit Sub". It returned
"AddBofQStandardPage" from project/module
"MasterPQSBofQCode1.xla!MenuRoutines".

Regards and thanks (foolishly).
 
Top