New menu item OnAction can't see macro?

[Ed]

I create a menu with two items in the list. The menu is created with a
Workbook_Open macro in the ThisWorkbook module. The macro called by the
menu item's OnAction also resides in the ThisWorkbook module. But when
selecting that menu item, I get the error "Macro 'Workbook!Macro' does not
exist." I call the same macro within the code of a
Workbook_SheetSelectionChange macro, again in the ThisWorkbook module, and
it works fine. What am I missing?

Ed

 

[Guest]

The sub called by OnAction has to reside in a Module. It can not reside in
ThisWorkbook or a sheet. Just the way it works. You could put a public sub in
a module that calls a sub in thisworkbook if you want to.

HTH

 

[JE McGimpsey]

You should put the macro in a regular code module.

ThisWorkbook is a workbook class module, which means that workbook event
procedures will by default call procedures within the class. When you
call a class procedure from outside the class (e.g., from a menu's
OnAction event), you need to fully qualify the reference, e.g.,

.OnAction ="Workbook1.xls!ThisWorkbook.foo"

From a menu item, you don't need to include the class module:

.OnAction = "Workbook1.xls!bar"

See

http://cpearson.com/excel/codemods.htm

 

[JE McGimpsey]

Actually, it *can* reside in the ThisWorkbook module (which is also a
"Module" - it's just a special instance of a class module, rather than a
regular/standard module). To use it, though, the reference has to
include the class name, e.g.:

Workbook1.xls!ThisWorkbook.foo

 

[Guest]

And you learn something new each and every day... Thanks...

 

[Ed]

Thank you. At least it was something I didn't know, rather than something I
screwed up!

Ed

 

[JE McGimpsey]

Should have been:

From a regular module, where non-event macros should reside,

 

[Jamie Collins]

Should have been:

From a regular module, where non-event macros should reside,

The implicit suggestion is that non-event code has no place in the
ThisWorkbook code module. I must disagree. I find ThisWorkbook far too
convenient; it would be a waste to restrict myself in this way. If I
have custom properties or methods which operate on the ThisWorkbook
object (e.g. an IsProtected property for the workbook), for me the
ThisWorkbook code module seems the most logical place for the code.

....But then I have an aversion to standard modules <g>. I do not use
them unless I have to (e.g. required for a UDF called from a cell
formula) and therefore do not use OnAction. Here is an alternative
approach using only the ThisWorkbook code module:

Option Explicit

Private WithEvents m_Menu1 As CommandBarButton
Private WithEvents m_Menu2 As CommandBarButton

Private Sub Workbook_Open()
With Application.CommandBars("Worksheet Menu Bar") _
..Controls(1).CommandBar
Set m_Menu1 = .Controls(1) ' File, New
Set m_Menu2 = .Controls(2) ' File, Open
End With
End Sub

Private Sub m_Menu1_Click( _
ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean _
)
MsgBox "Menu1 handler"
End Sub

Private Sub m_Menu2_Click( _
ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean _
)
MsgBox "Menu2 handler"
End Sub

Jamie.

--

 

[JE McGimpsey]

FWIW, since your technique inherently breaks cross-platform apps, it
would be worthless to me and most of my clients.

My comments were directed at what I perceived as the knowledge
level/technique employed by the OP, who wished to use OnAction. There
are, of course, almost always alternatives.

Even if I used your technique, I would probably still use a separate
class module rather than ThisWorkbook - it seems cleaner to me. De
gustibus non disputandum est (as your aversion to standard modules
attests).

 

[Jamie Collins]

since your technique inherently breaks cross-platform apps, it
would be worthless to me and most of my clients.

Could you expand on that please?

if I used your technique, I would probably still use a separate
class module rather than ThisWorkbook - it seems cleaner to me.

Interesting. I don't see the advantage of using a class module. There
would be no code re-use within the project; you'd only need once
instance. There is unlikely to be reuse between projects. Encapsulation
for its own sake? It seems to me that if something is 'workbook level'
it is more 'logical' to me to have it in the ThisWorkbook module.
Jamie.

--

 

[JE McGimpsey]

ActiveX control events work only in the Windows environment. I get paid
pretty well to make my apps work in both WinOffice and MacOffice.

 

[JE McGimpsey]

Like I said, there's no accounting for taste...

 

[Jamie Collins]

ActiveX control events work only in the Windows environment. I get paid
pretty well to make my apps work in both WinOffice and MacOffice.

I get it now. I'm so MS-centric these days I thought you meant cross
Windows platforms. Lucrative market, eh? Thanks for the tip!
Jamie.

--

 

[JE McGimpsey]

more like a niche market...