How to share custom command and macro behind?

Question

I developed a macro and made a custom command in a custom toolbar in Excel 2003. I THOUHGT when people copy paste my Excel file and click on the command, it should work, however it is not the case. People will have to re-assign the macro in their newly created Excel file.

I guess there is a better way to distribute your macro? How to make your macro and the custom toolbar and command available to your co-workers?

Answer 1

Hello,

In this case, we should programmatically create the commandbar button and programmatically assign the macro sub to the button, we could take use of Workbook_Open() event.

Please check following code:
Private Sub Workbook_Open()
Dim cb As CommandBar
Dim b As CommandBarButton
 
Set cb = Application.CommandBars("Standard")

  If Application.CommandBars("Standard").FindControl(Type:=msoControlButton, Tag:="TestButton").BuiltIn = False Then
  Exit Sub
  End If
 

Set b = cb.Controls.Add(msoControlButton, , , , True)
b.Caption = "Hello"
b.FaceId = 234
b.Tag = "TestButton"
b.Style = msoButtonIconAndCaption
b.OnAction = "Test"
End Sub

Test is the macro you want to assign to the button.

Thanks.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.