More Macro / AddIn Troubles

[kissfrito]

I want to create an addon so that I can distribute a macro to others. I'm doing the following:

1) I open a brand new work book, push alt + f11 to go to VB editor, and insert my code in a new module. I compile it.
2) I insert the following code in the "ThisWorkBook" code section and compile it. The point of this code is to add a tool bar to run my macro when a worksheet opens, and delete it when it closes:


Private Sub Workbook_Open()
AddNewToolBar
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteToolbar
End Sub

Sub AddNewToolBar()
Dim ComBar As CommandBar, ComBarContrl As CommandBarControl
On Error GoTo ErrorHandler
On Error Resume Next
CommandBars("Count Bins Toolbar").Delete
Set ComBar = CommandBars.Add(Name:="Count Bins Toolbar", Position:= _
msoBarFloating, Temporary:=True)
ComBar.Visible = True

Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton)
With ComBarContrl
.Caption = "countBins"
.Style = msoButtonCaption
.TooltipText = "Run Macro1"
.OnAction = "countBins"
End With
Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & vbCr & Err.Description
Exit Sub
End Sub


Sub DeleteToolbar()
On Error Resume Next
CommandBars("My Toolbar").Delete
End Sub

3) I save it as an addin and place it in the addin folder.

When I try to use it in another work book, the toolbar opens up fine, but when I go to click on the macro I get a weird message:

'countbinstoolbar.xls' could not be found. Check the spelling of the file name and verify that the file location is correct. If you are trying to open the file from your list of most recently used files on the File menu, make sure that file has not been moved, removed or deleted.'

When I try to open this on my friends computer, nothing happens, the toolbar doesn't even get added. Does anyone know what I could be doing wrong? We're both using excel 2003 btw.

Kiss

 

[fecurtis]

This question can also be found here:
http://www.excelforum.com/showthread.php?p=1926764&posted=1#post1926764

 

[kissfrito]

alright like I said on the other board sorry about that. I figured out that my main problem is that for whatever reason the toolbar just isn't getting added. forget what I said before about that error message appearing, that was an old tool bar that I forgot to delete which happened to have the same name as the one for the macro I'm trying to create.