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.'
I should say that 'countbinstoolbar.xls' isn't even the name of the addin (countbins) and was a file that I created a couple of hours ago when I was playing with that I eventually deleted. So I have no idea why that's on there.
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
Bookmarks