Button, but no code

A

Aaron Fude

Hi,

I'm supplying my colleagues with an addin but also with a spreadsheet that
has a button on it. I would prefer it if the button could call the code from
the addin rather than a callback within the spreadsheet. This would save
them the "Would you like to enable macros?".

Very many thanks in advance,

Aaron
 
B

Bob Phillips

Add the button to a toolbar when the add-in starts. Add this code to the
ThisWorkbook code module of the add-in

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim NewItem As CommandBarControl
Dim oCB As CommandBar
Dim NewItemName As String

Set oCB = Application.CommandBars("Formatting")

sbutton = "myButton"

On Error Resume Next
oCB.Controls(sbutton).Delete
On Error GoTo 0

End Sub

Private Sub Workbook_Open()
Dim NewItem As CommandBarControl
Dim oCtl As CommandBarControl
Dim oCB As CommandBar
Dim NewItemName As String

Set oCB = Application.CommandBars("Formatting")

sbutton = "myButton"

On Error Resume Next
oCB.Controls(sbutton).Delete
On Error GoTo 0

Set oCtl = oCB.Controls.Add(Type:=msoControlDropdown, _
temporary:=True)
With oCtl
.Caption = sbutton
.OnAction = "myMacro"
.BeginGroup = True
End With

End Sub


--

HTH

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

Aaron Fude

Nice. Thank you.

Bob Phillips said:
Add the button to a toolbar when the add-in starts. Add this code to the
ThisWorkbook code module of the add-in

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim NewItem As CommandBarControl
Dim oCB As CommandBar
Dim NewItemName As String

Set oCB = Application.CommandBars("Formatting")

sbutton = "myButton"

On Error Resume Next
oCB.Controls(sbutton).Delete
On Error GoTo 0

End Sub

Private Sub Workbook_Open()
Dim NewItem As CommandBarControl
Dim oCtl As CommandBarControl
Dim oCB As CommandBar
Dim NewItemName As String

Set oCB = Application.CommandBars("Formatting")

sbutton = "myButton"

On Error Resume Next
oCB.Controls(sbutton).Delete
On Error GoTo 0

Set oCtl = oCB.Controls.Add(Type:=msoControlDropdown, _
temporary:=True)
With oCtl
.Caption = sbutton
.OnAction = "myMacro"
.BeginGroup = True
End With

End Sub


--

HTH

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

Bob Phillips

It's a pleasure.

--

HTH

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