Identical menuitems referring to wrong workbook

  • Thread starter Thread starter R Avery
  • Start date Start date
R

R Avery

I have two add-ins that create their own menus. In fact, I use Walkenbach's
MenuMaker to do this. This problem happens when they are both installed and
loaded into memory.

I have a sub-menu in both custom menus that have the same captions and same
items, but refer to procedures only within the respective add-in; the
add-ins do not reference each other at all.

The problem is that when both menus exist, the submenuitems in both menus
call the *same* procedure. SubmenuItem 1 in Menu A and SubmenuItem 1 in
Menu B both call the procedure in Workbook A, even though the .OnAction
property refers to the correct (separate) workbooks.

I suspect that it has to do with the fact that the structure of the menu is
the same, and/or the name of the procedure it calls is the same. But why
does this happen? I have tried changing the procedure names in the
workbooks so that they are not identical, and this fixes the problem.... but
is there any other way around it?

Any help would be most appreciated.
 
How did you refer to the .onaction property?

..onaction = thisworkbook & "!mymacro"
or just
..onaction = "mymacro"

That's where I'd look first absent any code to check.
 
first, a correction to my question:
..onaction = thisworkbook & "!mymacro"
should have been:
..onaction = thisworkbook.name & "!mymacro"

But I couldn't duplicate your experience.

I created two workbooks (book8.xls and book9.xls).

In book8.xls's project:

Option Explicit
Sub auto_open()
Call CreateMenu
End Sub
Sub auto_close()
Call DeleteMenu
End Sub
Sub CreateMenu()

Dim iCtr As Long
Dim myCtrl As CommandBarControl
Dim myBTN As CommandBarButton
Dim myCaptions As Variant
Dim myMacs As Variant

myCaptions = Array("test1a", "test1b")
myMacs = Array("test1a", "test1b")

Call DeleteMenu

Set myCtrl = Application.CommandBars(1).Controls.Add _
(Type:=msoControlPopup, _
before:=Application.CommandBars(1).Controls.Count, _
temporary:=True)
myCtrl.Caption = "test1"

For iCtr = LBound(myCaptions) To UBound(myCaptions)
Set myBTN = myCtrl.Controls.Add(Type:=msoControlButton, _
temporary:=True)
With myBTN
.OnAction = "book8.xls!" & myMacs(iCtr)
.Caption = myCaptions(iCtr)
.FaceId = 103
If iCtr Mod 3 = 2 Then
.BeginGroup = True
Else
.BeginGroup = False
End If
End With
Next iCtr

End Sub

Sub DeleteMenu()
On Error Resume Next
Application.CommandBars(1).Controls("test1").Delete
On Error GoTo 0
End Sub

Sub test1a()
MsgBox ThisWorkbook.Name
End Sub
Sub test1b()
MsgBox ThisWorkbook.Name
End Sub

In book9.xls's project (very similar code):

Option Explicit
Sub auto_open()
Call CreateMenu
End Sub
Sub auto_close()
Call DeleteMenu
End Sub
Sub CreateMenu()

Dim iCtr As Long
Dim myCtrl As CommandBarControl
Dim myBTN As CommandBarButton
Dim myCaptions As Variant
Dim myMacs As Variant

myCaptions = Array("test1a", "test1b")
myMacs = Array("test1a", "test1b")

Call DeleteMenu

Set myCtrl = Application.CommandBars(1).Controls.Add _
(Type:=msoControlPopup, _
before:=Application.CommandBars(1).Controls.Count, _
temporary:=True)
myCtrl.Caption = "test2"

For iCtr = LBound(myCaptions) To UBound(myCaptions)
Set myBTN = myCtrl.Controls.Add(Type:=msoControlButton, _
temporary:=True)
With myBTN
.OnAction = "book9.xls!" & myMacs(iCtr)
.Caption = myCaptions(iCtr)
.FaceId = 103
If iCtr Mod 3 = 2 Then
.BeginGroup = True
Else
.BeginGroup = False
End If
End With
Next iCtr

End Sub

Sub DeleteMenu()
On Error Resume Next
Application.CommandBars(1).Controls("test2").Delete
On Error GoTo 0
End Sub

Sub test1a()
MsgBox ThisWorkbook.Name
End Sub
Sub test1b()
MsgBox ThisWorkbook.Name
End Sub


===========
I hardcoded the names in the .onaction and it found the correct one each time.

Any chance that the toolbar is hanging around from before and always pointed to
the incorrect macro???
 
I have recreated the problem, using your existing code almost exactly.
Save those two files as Add-ins, put them in the add-in folder, and load
them.
Before loading them you must change the workbook name in the code to ".xla"
instead.

By doing only that, I experience the problem i posted about.

For reference, I am using Excel10.
 
I missed that that example was an addin and the first sentence in your original
post, too! (sorry) I used .xls and didn't have the trouble.

But I could duplicate the problem if I used .xla's.

It looks like a bug to me, but maybe it's by design <bg>.

I think the workaround is to use unique names. But you knew that already.

(also xl2002)



R said:
I have recreated the problem, using your existing code almost exactly.
Save those two files as Add-ins, put them in the add-in folder, and load
them.
Before loading them you must change the workbook name in the code to ".xla"
instead.

By doing only that, I experience the problem i posted about.

For reference, I am using Excel10.
Click to expand...
 
Back
Top