Custom toolbar button to run VBA add-in

[chrism]

How do I create a custom toolbar button to execute VBA
script in and add-in?

I know how to create a custom toolbar button to run a
macro, but how do you assign it to run VBA code?

 

[Frank Stone]

hi,
excel already has tool bars for that.
VB...
Right click the main excel tool bar then click customize.
then click the tool bar tab. read down and cleck vb.

macro...
Right click the main excel tool bar then click customize.
click the command tab. in the right pane select macro.
click and drag the smiley face to the tool bar(any where
you want it. you can move it later if you want) click the
modify button. click assign macro.
You can also change the smiley face to another icon.
click change button image.
or you can design your own. click edit button image.
(ever played with bitmaps?)
Custom tool bar....
in the customize window click tool bar tab. click new
button. name it. click the commands tab. click and drag
icons to new tool bar.
custom menu....
in the customize window click command tab. in right pane
click menu. in the left pane click and drag new menu to a
tool bar or to new tool bar. left click the new menu. name
it. click the down arrow on the new menu.(it drops down to
blank) click the command tab. click and drag commands to
new menu. click the down arrow on the new menu.
regards
Frank

 

[Bob Phillips]

I would dynamically create the toolbar when opening the add-in.

Here is some sample code to create a toolbar button on the Formatting as
suggested. This code would go in the ThisWorkbok code module of the addin.

I would also add my usual corollary that to see what FaceIds are available,
visit John Walkenbach's site at http://j-walk.com/ss/excel/tips/tip67.htm

Option Explicit

Dim sMenu As String

Private Sub Workbook_BeforeClose(Cancel As Boolean)

sMenu = "myButton"

On Error Resume Next
Application.CommandBars("Formatting").Controls(sMenu).Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim oCtl As CommandBarControl
Dim newMenu As Object 'CommandBarControl
Dim ctrlButton As Object 'CommandBarControl

sMenu = "Margin Calculator"

On Error Resume Next
Application.CommandBars("Formatting").Controls(sMenu).Delete
On Error GoTo 0

Set oCB = Application.CommandBars("Formatting")
Set oCtl = oCB.Controls.Add(Type:=msoControlButton, temporary:=True)

With oCtl
.BeginGroup = True
.Caption = sMenu
.FaceId = 197
.Style = msoButtonIconAndCaption
.OnAction = "myMacro"
End With

End Sub