Many Menu onAction on different XLA file

  • Thread starter Thread starter pcouas
  • Start date Start date
P

pcouas

Hi,

I search to create a Menu on two XLA, but with same Sub Name.
Currently Excel found first XLA in mémory

' add menuitem to menu
With cbMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&B5"
.OnAction = "b5sqlstd.xla!cree_requete"
End With
' add menuitem to menu
With cbMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&T5"
.OnAction = "t5sqlstd.xla!cree_requete
End With

Regards
Philippe
 
I guess my first response would be: rename one of the macros.

If that's not possible, can you rename the module in which they reside?

This worked for me (xl2002):

Option Explicit
Sub testme02()

Dim cbMenu As CommandBar
Set cbMenu = Application.CommandBars("worksheet menu bar")
' add menuitem to menu
With cbMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&B5"
.OnAction = "book1.xla!module1.testme01"
.FaceId = 17
End With
' add menuitem to menu
With cbMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&T5"
.OnAction = "book11.xla!module2.testme01"
.FaceId = 19
End With

End Sub

I used this as testme01 in both .xla's:

Option Explicit
Sub testme01()
MsgBox "hi from " & ThisWorkbook.FullName
End Sub

If you can't do either, then I don't have another option.
 
Back
Top