The same event handler for two CommandBar buttons

[Guest]

The same event handler for two CommandBar buttons
I add custom command

There are who similar XLA AddIns which add commandbars. The names of
commandbars are different and are formed from the name of the corresponding
xla file.
But the event handlers have the same names; it leads to the fact that
clicking on a button of any commandbar calls identical event handler. To be
exact, the handler from the file, which has been opened in the first
instance, is called.

I suppose that the same ID of the buttons causes the problem, but I failed
to add a button with my own ID.

Is there a solution for this issue?
Thank you!

Here is my code:


Option Explicit
Option Compare Text

Public strCommandBarName As String
Const strParameters = "Parameters"

Sub AddCommandBar()

Dim c As CommandBar
Dim CB As CommandBarButton
Dim cp As CommandBarPopup

On Error Resume Next

strCommandBarName = ThisWorkbook.Name
strCommandBarName = Replace(strCommandBarName, ".xla", "")

Set c = Application.CommandBars(strCommandBarName)

If Not c Is Nothing Then
Application.CommandBars(strCommandBarName).Delete
End If

Set c = Application.CommandBars.Add(strCommandBarName, msoBarTop, False,
False)

c.Enabled = True
c.Visible = True

Set CB = c.Controls.Add(msoControlButton, 1)
CB.Style = msoButtonIconAndCaption
CB.Caption = strParameters
CB.OnAction = ThisWorkbook.Name & "!" & "OnActionParameters"
CB.FaceId = 304

End Sub
Sub DeleteCommandBar()
Dim c As CommandBar
On Error Resume Next
Set c = Application.CommandBars(strCommandBarName)
If Not c Is Nothing Then
Application.CommandBars(strCommandBarName).Delete
End If
End Sub

Private Sub OnActionParameters()
MsgBox ThisWorkbook.Name
End Sub

Function GetMyIndex() As String
Dim FileName As String
FileName = ThisWorkbook.Name
FileName = Replace(FileName, ".xla", "")
GetMyIndex = Mid(FileName, Len("ReplaceOnMask") + 1)
End Function

 

[Guest]

You may have to change the properties in the buttons. To do this you have to
enter design mode.

1) On the spreadsheet, go to View Menu - Toolbars - Control Tool box.
2) Select Design Mode icon
3) Click on either of the two buttons.
4) Select Properties on the Control tool box.

You have to press the Design Mode Icon a second time to leave design mode
and to test the changes.

 

[Guest]

Thank you!
I would prefer to guarantee appropriate functionality programmatically.
These are Add-ins, and they create commandbars every time Excel starts up.

 

[Bob Phillips]

Give them unique names?

I have had this problem with Help file modules where someone else's addin
used the same name. Since then I include a project id in the procedure
names.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

That's very good idea and it is so for the present. But I would like to
preserve Add-in's feature that it can be duplicated many times on the same PC
by any user for working with various data sets, which are stored and
maintained in the hidden sheets of Add-ins.

 

[Bob Phillips]

I suppose that you could always use application.run to call the addin macro

Application.Run "'myAddin.xla'!myMacro"

or even be module specific if you duplicate macro names ihn the addin


Application.Run "'myAddin.xla'!myModule.myMacro"

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

You can see in the code below that I did in this way. I explored the object’s
properties of two CommandBars, which existed together at the same time, and
OnAction of both contained the same handler, from the first Add-in.