Send menu call to explicit Sub

  • Thread starter Thread starter Stuart
  • Start date Start date
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.
 
.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.
 
Just to be sure, can one reference the module in that statement,
ie
..OnAction = "MyWorkbookName.xls! _Module1_AddBofQStandardPage"

Regards.
 
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.
 
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.
 
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).
 
Back
Top