Macro-Toolbarbutton

J

John

Hello,

I am trying to add a toolbar button that will run a macro
in any file. The macro that I am trying to run is from a
custom Add-In I have created. This Add-In macro can run
from any file. How can I get the toolbar button to
reference the Add-In Macro, additionally I would like the
toolbar button to appear on my toolbar for every Excel
file I have open (somewhat like a default toolbar button).
 
B

Bob Flanagan

John, change the workbook property of your file from True for IsAdd-in to
False. Make certain you do not have option private module at the top of the
module containing the procedure to be assigned to the button. Select View,
Toolbars, Customize. Select the button and then right click on it. Use the
menu item for macro to assign a macro to the button. Then exit the menus.
Change the add-in property back to true.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel
 
B

Bob Phillips

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

Set a reference to your addin in the VBE, Tools>References.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

Sorry having problems.

As I was saying, you can set a reference to your addin and reference your
procedures within that, oir better (IMO) you could create that button from
within the addin itself, and call the macro directly.


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



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top