Creating an AddOn

In Excel you need to add the button at startup and remove it when you're done.  I would suggest that you create a new module in VBA and place an Auto_Open and an Auto_Close procedure in it.

Are you very experienced with VBA?  Looking for the code now.

I am ok with vba, i can't find the code to do it though..............

I was trying to modify the code below but it didn't seem to do anything (Didnt get any error's either), does this need to be in a regular module or in 'this workbook'?

Sub Auto_Open()
'On Error GoTo Oops
    Dim NewMenu1 As Menu
    Dim NewMenu2 As Menu
   
    'Place a "Compare" Menu on the Menubar
    Set NewMenu1 = MenuBars(xlWorksheet).Menus.Add( _
        Caption:="&Check KBE", before:="Data")
    Set NewMenu2 = MenuBars(xlModule).Menus.Add( _
        Caption:="&Check KBE", before:="Data")
       
    NewMenu1.MenuItems.Add _
        Caption:="&Check KBE", OnAction:="CheckKbes"
       
    NewMenu2.MenuItems.Add _
        Caption:="&Check KBE", OnAction:="CheckKbes"
       
    'HideAll
   
    'Put release in welcome screen
'    With ThisWorkbook.Sheets("Welcome")
'        .Cells(4, 2).Value = "Welcome"
'        .Cells(5, 3).Value = "to"
'        .Cells(7, 3).Value = "Workbook Compare Utility!"
'        .Cells(9, 4).Value = "Version " & cVersion
'        .Cells(13, 3).Value = _
'                        "Please use the Compare item in Menu to run utility."
'        .Cells(15, 4).Value = "© Lucho"
'        .Cells(15, 4).Font.Bold = True
'        .Cells(15, 4).Font.Italic = True
'        .Cells(15, 4).Font.Size = 10
'    End With
   
    'don't prompt to save changes in this workbook when exiting
'    ThisWorkbook.Saved = True

'Yeah:
'    Exit Sub
'Oops:
'    MsgBox "Error in Auto_Open. " & Err & Chr$(13) & Chr$(10) & Error(Err)
'    Resume Yeah
End Sub

--------------------------------------------------------------------------------------
Sub Auto_Close()
'On Error GoTo Oops
    'Remove the "Compare" Menu from the MenuBar
    MenuBars(xlWorksheet).Menus("Check KBE").Delete
    MenuBars(xlModule).Menus("Check KBE").Delete
'Yeah:
'    Exit Sub
'Oops:
'    MsgBox "Error in Auto_Close. " & Err
'    Resume Yeah
End Sub

Put this in the Workbook open event:

Sub create_btn()
On Error Resume Next
Application.CommandBars("cmbTools").Delete
    With Application.CommandBars.Add("cmbTools")
        With .Controls.Add(msoControlButton)
            .Style = msoButtonCaption
            .Caption = "MYButton"
            .OnAction = "MyButton_Click"
            .Visible = True
        End With
        .Visible = True
        .Position = msoBarBottom
    End With
End Sub
   
Sub MyButton_Click()
MsgBox "I was Clicked"
End Sub

Select allOpen in new window

So i am able to test in xl2007 and it appears to work, how will this act in 2003?

slick, thanks!

I found this code years ago from a Microsoft MVP.  Unfortunately, I can't find my original copy, so I can't give them credit.  

Copy all the code below into your new module.  It contains all the procedures you need to add/remove items from toolbars and create/delete a new toolbar

Sub Auto_Open()
Public OriginalMenuBar As Object

' In the AutoOpen of the AddIn you want to add your toolbar, and then each button.

' Add new toolbar.
'
    AddDeleteToolbars "TOOLBAR_NAME", TEMPORARY? True/False 
    ' EXAMPLE:  AddDeleteToolbars "Tools", False

'Remove and hide items from Toolbars
    RemoveItemFromToolbar "TOOLBAR_NAME", "CONTROL_NAME"    
    'EXAMPLE:  RemoveItemFromToolbar "Standard", "Permission (Unrestricted Access)"

' Now add the buttons.

    AddItemToToolbar "TOOLBAR_NAME", "BUTTON_NAME", "MACRO_NAME", POSITION, FACE_ID
    'EXAMPLE:  AddItemToToolbar "Tools", "InsertColorLogo", "InsertColorLogo", 1, 352
    
 
'Set Keyboard shortcuts   
    Application.OnKey "+^c", "MACRO_NAME" 'SHIFT+CTRL+c
   

'================================================================================
' Some more examples:
' ===================
' Add a button to the Standard toolbar.
    'AddItemToToolbar "Standard", "X", "X", 1, 18
' Add a button to the VisualBasic toolbar.
    'AddItemToToolbar "Visual Basic", "X", "X", 1, 18
' Add a button to the Formatting toolbar.
    'AddItemToToolbar "My Toolbar", "X", "X", 1, 18
End Sub

Sub Auto_Close()


' When closing, it is important to clean up.
' At this point we need to remove all command buttons and toolbars we created during the AutoOpen process.
    
' Remove controls  
   RemoveItemFromToolbar "TOOLBAR_NAME", "CONTROL_NAME"  

 'Remove created toolbars 
   AddDeleteToolbars "Tools", True
    

'================================================================
' Here is an example of deleting buttons from different toolbars:
    ' RemoveItemFromToolbar "Standard", "X"
    ' RemoveItemFromToolbar "Visual Basic", "X"
    ' RemoveItemFromToolbar "Formatting", "X"
End Sub

Sub AddDeleteToolbars(strToolbarName As String, Optional bolDelete As Boolean)
    
' NOTE: We will error if the toolbar already exists.
    On Error Resume Next
    
    Dim cbarApp    As CommandBars
    Dim cbarMine
    
' Get the application CommandBar object.
    Set cbarApp = Application.CommandBars
    
' If the DELETE switch is OFF...
    If Not bolDelete Then
' add the toolbar with the name strToolbar...
        Set cbarMine = CommandBars.Add(Name:=strToolbarName, _
                                       Position:=msoBarTop, _
                                       Temporary:=True)
        cbarMine.Visible = True
    Else
' else we delete the toolbar.
        cbarApp.Item(strToolbarName).Delete
    End If
End Sub

Sub RemoveItemFromToolbar(strTBName As String, _
                          strButtonName As String)
                     
    Dim cbarMenu    As CommandBars
    Dim cctlControl As CommandBarControl

' Grab the application CommandBars object.
    Set cbarToolsMenu = Application.CommandBars
    
' DELETE: Toolbar button.
' Loop through the CommandBars.
    For Each cctlControl In cbarToolsMenu(strTBName).Controls
        With cctlControl
' Check to see if the toolbar is found.
            If .Caption = strButtonName Then

' If found, remove the command from the menu.
               .Delete

            End If

        End With
    Next cctlControl
End Sub
                     

Sub AddItemToToolbar(strTBName As String, _
                     strButtonName As String, _
                     strMacroName As String, _
                     intInsertPosition As Integer, _
                     lngFaceID As Long)
                     
    Dim cbarMenu    As CommandBars
    Dim cctlControl As CommandBarControl
    Dim bolFound    As Boolean

' Grab the application CommandBars object.
    Set cbarMenu = Application.CommandBars

' First, look for button on the toolbar already.
    For Each cctlControl In cbarMenu(strTBName).Controls
        With cctlControl
' Look for the toolbar item.
            If .Caption = strButtonName Then
' Button is found on the toolbar.
                bolFound = True
            End If
        End With
    Next cctlControl

' If the button is not found...
    If Not bolFound And intInsertPosition > 0 Then

' Add the command to the menu.
        Set cctlControl = cbarMenu(strTBName).Controls.Add _
                    (Type:=msoControlButton, _
                     Before:=intInsertPosition)

' Name the command.
' Set its values.
        With cctlControl
            .FaceId = lngFaceID
            .Caption = strButtonName
            .OnAction = strMacroName
            .TooltipText = strButtonName
            .Enabled = True
        End With
    End If
End Sub

Select allOpen in new window