Share a toolbar that calls macro from an add-in

I have created some macros that need to be used in several workbooks by more than one users, so I put them in an add-in and gave them instructions on how to get the add-in. I also created a toolbar with buttons calling those macros, attached it to a workbook the users can open, and instructed them to open that workbook once to get the toolbar.

Both the add-in and the toolbar workbook are on a network drive that my users can access, but when they get the add-in, it gets copied to their own add-in folder.

Here's my problem:
When a user tries to click on one of the buttons in the toolbar, the button tries to call the macro using the path that would be valid on my computer to get to my add-in folder. This path includes my user name, so it doesn't go to their own add-in folder: it goes to my add-in folder on that computer if I ever logged in on that particular machine, or it goes nowhere.

When I created the button, I attached the macros by referring to the add-in workbook by name only, without referring to a path, so I don't know why MY path ended up defined on that toolbar.

How can I go around this? I manually relinked all the buttons for that user, and I can do it for the other users (there aren't that many users nor that many buttons), but I'd rather avoid it: I'll have to re-do it if the toolbar is updated and it just seems like more work than should be necessary.

I'm also opened to suggestions on a better way to share those macros. They can't be attached to the workbooks because we use new workbooks every year - they're emailed to us by the people who create them and for a variety of reasons, pasting everything into a workbook of our own is not a good option.

Thanks for any help,

Brigitte

It's much better to add your ToolBar within the code, there's many examples this is one I have edited from one of my existing workbooks.

Place this in a Standard Module, I have named the module mToolBars and it is added to the Data menu.

Option Explicit
'////////////////////////////////////////////////
'
'
'   This module contains all code specific to the
'   creation and deletion of the various buttons
'   that are added to Excel's menus.'
'
'////////////////////////////////////////////////

Const APPNAME As String = "MyAddin"

Public Sub AddButtonsMain()
    On Error GoTo errHandler
    Call mToolbars.RemovesButtons
    'Add a button to the Data menu.
    AddButton Application.CommandBars("Data")
    Exit Sub
errHandler:
   MsgBox "Toolbar could not be created"
End Sub


Private Sub AddButton(ComBar As CommandBar)
    'Adds the "JR Applications" button to the specified commandbar

    Dim ComPop As CommandBarPopup, ComBut As CommandBarButton
    Dim lngCurrentRow As Long
    Dim lngLastRow As Long

    On Error GoTo errHandler

    Set ComPop = ComBar.Controls.Add(Type:=msoControlPopup, before:=1)
    ComPop.BeginGroup = True
    ComPop.Caption = APPNAME
    Set ComBut = ComPop.Controls.Add(msoControlButton)
    ComBut.BeginGroup = True
    ComBut.Caption = "Load Form"
    ComBut.OnAction = "ShowDataForm"
    ComBut.Style = msoButtonIconAndCaption
    ComBut.FaceId = 137

    Exit Sub

errHandler:
  MsgBox "The toolbar could not be added"
  End Sub


Public Sub RemovesButtons()
    Dim ComBar As CommandBar
    Dim ComCtl As CommandBarControl

    On Error GoTo errHandler
    For Each ComCtl In Application.CommandBars("Data").Controls
        If ComCtl.Caption = APPNAME Then ComCtl.Delete
    Next ComCtl
    Exit Sub

errHandler:
 
End Sub

This code is important to make sure that the ToolBar is loaded correctly & unloaded when not needed.

Option Explicit

Private Sub Workbook_AddinInstall()
    Call mToolbars.AddButtonsMain
End Sub

Private Sub Workbook_AddinUninstall()
Call mToolbars.RemovesButtons
End Sub

Here's an example that adds it's own Toolbar

try changing the Username part of the file location by adapting this code

Private Declare Function GetUserName Lib "advapi32.dll" _
    Alias "GetUserNameA" (ByVal lpbuffer As String, _
                          nsize As Long) As Long

Sub get_user_name()
Dim lpbuff As String * 25
Dim ret As Long
Dim strName As String
ret = GetUserName(lpbuff, 25)
strName = Left(lpbuff, InStr(lpbuff, Chr(0)) - 1)
End Sub

Something Like "C:\Documents and Settings\........" & strName

P.s. code thanks to ROYUK (Legend)

HTH