Excel Add-in Issues

[adim654]

So I wrote a couple of simple macros that are used for formatting worksheets and changing cases of text. I want to make this an add-in that I can give to others to use. I have a key command set for one of the macros, and they all have buttons on the toolbar to execute them. When I create the add-in I am able to select it in a new workbook, but I can't get access to the macros at all. The key command doesn't work, the buttons are not on the toolbar.

I have no idea what I am doing wrong, I can't seem to find any help on the issue, whether it be on these forums or in the VBA for Dummies book. Can anyone help?

 

[anthonya2369]

I am assuming you created buttons on the ribbon. If so, what version of Excel are you using. Also, how did you create the buttons on the ribbon? VBA or via customui?

 

[adim654]

I'm using Excel 2010. I created buttons on the Quick Access Toolbar by going in the preferences menu for the toolbar and selecting my macros from the list of available macros.

 

[Wookiee]

I'm not sure if the fact that you've got your file saved as add-in will affect this solution, but I've got some code which will automatically create a macro toolbar each time the file containing it is opened. Once it has run, the macro toolbar will appear in the Add-Ins section of the Ribbon (can't say off the top of my head if there's much difference in 2010 or 2013 as I primarily use 2007, but I do believe it will still work at least in 2010).

Sub Auto_Open()

Call MightyToolBar

End Sub

Sub MightyToolBar()

Dim cBar As CommandBar, cControl As CommandBarControl

' Remove Toolbar
Call RemoveMighty

' Create Toolbar
Set cBar = Application.CommandBars.Add
cBar.Name = "Mighty Macros"
cBar.Visible = True

' Add Control
Set cControl = cBar.Controls.Add
With cControl
.FaceId = 994  'Choose macro icon
.OnAction = ("Macro_1")  'Enter your macro name here
.TooltipText = "This is what macro 1 will do for you"   'Brief text description of macro function​
End With

' Add Control
Set cControl = cBar.Controls.Add
With cControl
.FaceId = 483
.OnAction = "SampleToolbar"
.TooltipText = "SampleToolbar"​
End With

End Sub

Sub RemoveMighty()

On Error Resume Next
Application.CommandBars("Mighty Macros").Delete

End Sub

 

[Wookiee]

I almost forgot. If you need a list of the FaceID icons, I created another sample toolbar macro which will let you review the icons. I used to look them up online, but there are over 500 of them and most websites I've seen don't have high-quality graphics, and many of the icons didn't look the same in the version of Excel I'm using.

This macro (a link to which I also included in my previous code) will prompt you for a starting number and an ending number, then create a toolbar where you can view each icon. If you hover your cursor over a specific icon, it will display the FaceID code.

If you want to choose another range of icons, click the first icon (a big black spade) and you will again be prompted for the start and end numbers. And once you're finished reviewing the icons, you can click the second icon (a red X) and it will remove the sample toolbar.

Sub SampleToolBar()
' Macro crafted 13 May 2011 by Jason B White

'Declare Variables
Dim cBar As CommandBar, cControl As CommandBarControl
Dim f As Integer, t As Integer, i As Integer

'Get Start and End Values
f = InputBox("What is the starting range?")
t = InputBox("What is the ending range?")

'Remove Toolbar
Call DeleteSampleToolBar

'Create Toolbar
Set cBar = Application.CommandBars.Add
cBar.Name = "Sample Icons"
cBar.Visible = True
    
'Add First Macro Button (Caption Indicates Start and End Range of Buttons Displayed)
Set cControl = cBar.Controls.Add
With cControl
    .Caption = f & " to " & t
    .FaceId = 483
    .OnAction = "SampleToolbar"
End With

'Add Control
Set cControl = cBar.Controls.Add
With cControl
    .FaceId = 923
    .OnAction = "DeleteSampleToolbar"
    .TooltipText = "Removes the Sample Toolbar"
End With

'Add Buttons for Specified Range of Face Values
For i = f To t
'Add Controls
Set cControl = cBar.Controls.Add
With cControl​
.Caption = i
.FaceId = i​
End With​
Next i

End Sub

Sub DeleteSampleToolBar()

On Error Resume Next
Application.CommandBars("Sample Icons").Delete

End Sub