command button in toolbar

In the Open Event of your Add-in you need to add code that positions your button adds a face ID Icon to the button and attaches a macro to the button.

Something like this:


Set MainMenu = CommandBars.Add(Name:="myMacro", Position:=msoBarFloating, Temporary:=True)
MainMenu.Visible = True

Set ctl = CommandBars("myMacro").Controls.Add(msoControlButton)
ctl.Style = msoButtonIconAndCaption
ctl.Caption = "Run Macro" 'Button label.
ctl.OnAction = "myColorRowData" 'Name of macro to run.
ctl.FaceId = 15
'Icons: 69=TV,10=ControlBoxOnSheet,11=123Bullets,12=BoxTypeBullets,13=flipPageOrientation,
'14=JustificationWidth,

ok..i added the code and now the problem is its saying it cant find the macro even though its sitting in the same project. Could this be because its an .xla file? The macro doesnt show up in the macro list when I try to run it under the Tools menu.

An addin is another workbook so you need to tell the button which workbook to get the code from!

Try:

"myAddinName.xla! myMacroName"

Also this will add a new item to the default Excel bar and than add items to its dropdown, just like if you clicked on the first item of the default bar "File" which than drops down that items options:



Sub myAdd_MyMenu_ToDefaultToolbar()
'Standard module code, like: Module1!
Dim myNewMainMenu, myNewMainMenuItem
'This adds a new item to the Default toolbar at the very top of the page, like: File Edit View...

On Error GoTo myErr
'Delete custom menu if it exists!
Call Remove_MyMenu

'Add a new item to the default toolbar!
With CommandBars("Worksheet Menu Bar")
Set myNewMainMenu = .Controls.Add(Type:=msoControlPopup, temporary:=True)
End With

'Name this new toolbar item!
myNewMainMenu.Caption = "MyMenu"

'Add a sub-menu item to the new menu list!
Set myNewItem1 = myNewMainMenu.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)

With myNewItem1
.Caption = "Un-Install"
.TooltipText = "Un-install this MyMenu from this toolbar!"
.Style = msoButtonCaption
.OnAction = "Remove_MyMenu" 'Name of macro to run.
End With

'Add a Second sub-menu item to the new menu list!
Set myNewItem2 = myNewMainMenu.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)

With myNewItem2
.Caption = "Run Test"
.TooltipText = "Runs the macro for this item!"
.Style = msoButtonCaption
.OnAction = "myTestItem" 'Name of macro to run.
End With
Exit Sub

myErr:
MsgBox "An error has occured, did not create menu items. " & Chr(13) & _
"Error number: " & Err.Number & Chr(13) & "Error Description: " & Err.Description, vbExclamation + vbOKOnly, "Error!"
Resume Next
End Sub

Sub Remove_MyMenu()
'Standard module code, like: Module1!
'Removes custom menu if it exists!

On Error Resume Next

CommandBars("Worksheet Menu Bar").Controls("MyMenu").Delete
On Error GoTo 0
End Sub

Private Sub myTestItem()
'Standard module code, like: Module1!

MsgBox "This is a test for a Sub-Menu item activation!"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'ThisWorkbook module code, Only!
'Removes custom menu if it exists!

Call Remove_My_Menu
End Sub

Private Sub Workbook_Open()
'ThisWorkbook module code, Only!
'Adds custom menu to this workbook.

Call myAdd_MyMenu_ToDefaultToolbar
End Sub

Private Sub Workbook_Deactivate()
'ThisWorkbook module code, Only!
'Removes custom menu if it exists!

Call Remove_MyMenu
End Sub

Private Sub Workbook_Activate()
'ThisWorkbook module code, Only!
'Adds custom menu to this workbook.

Call myAdd_MyMenu_ToDefaultToolbar
End Sub

Thanks for the code, I'm gonna try that...as for the issue i moved the macro i was trying to call from thisworkbook to a separate module and now it works fine. But I am definitely gonna play around with this code...its a bit handier than having the toolbar float around in the way everytime you open excel. thanks

I got a couple errors just running the above code...one is error 91 - 'Object variable or Without block variable not set' ...the other is 424 'object required'.

I did not get any errors?

It was written in Office 2000 and updated on Office 2002 and I just ran it in 2003 xp.

All with the standard Refrences: VBA, Excel Object Library, OLE Automation and Office Object Library.

When you debug which lines of code cause your errors?

gives me the error 91...looks like it should be an = something after the statement?

also gives the same error 91

give the 424 Object Required error

And it seems like just about every line after that gives the 424 error.

Any ideas? Do I need to move it to its own module or something?

This works for me ...



<font face=Tahoma New><SPAN style="color:#00007F">Sub</SPAN> myAdd_MyMenu_ToDefaultToolbar()
    <SPAN style="color:#00007F">Dim</SPAN> myNewMainMenu <SPAN style="color:#00007F">As</SPAN> CommandBarPopup, myNewMainMenuItem <SPAN style="color:#00007F">As</SPAN> CommandBarButton
    <SPAN style="color:#00007F">Call</SPAN> Remove_My_Menu
    <SPAN style="color:#00007F">With</SPAN> CommandBars("Worksheet Menu Bar")
        <SPAN style="color:#00007F">Set</SPAN> myNewMainMenu = .Controls.Add(Type:=msoControlPopup, temporary:=True)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    myNewMainMenu.Caption = "MyMenu"
    <SPAN style="color:#00007F">With</SPAN> myNewMainMenu.CommandBar.Controls.Add(Type:=msoControlButton)
        .Caption = "Un-Install"
        .TooltipText = "Un-install this MyMenu from this toolbar!"
        .Style = msoButtonCaption
        .OnAction = "Module1.Remove_My_Menu"    <SPAN style="color:#007F00">'Name of macro to run.</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">With</SPAN> myNewMainMenu.CommandBar.Controls.Add(Type:=msoControlButton)
        .Caption = "Run Test"
        .TooltipText = "Runs the macro for this item!"
        .Style = msoButtonCaption
        .OnAction = "Module1.myTestItem"    <SPAN style="color:#007F00">'Name of macro to run.</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> Remove_My_Menu()
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    CommandBars("Worksheet Menu Bar").Controls("MyMenu").Delete
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> myTestItem()
    MsgBox "This is a test for a Sub-Menu item activation!"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>



Don't forget to make the routine names match .. Remove_My_Menu from Remove_MyMenu, or vice versa.

HTH