More Macro / AddIn Troubles

kissfrito

New Member
Joined
Jun 3, 2008
Messages
30
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
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

kissfrito

New Member
Joined
Jun 3, 2008
Messages
30
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,116
Messages
5,546,033
Members
410,721
Latest member
adi772
Top