Button, but no code
 

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

Button, but no code
 

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)

"Aaron Fude" wrote in message
...
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

Button, but no code
 

Nice. Thank you.

"Bob Phillips" wrote in message
...
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)

"Aaron Fude" wrote in message
...
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

Button, but no code
 

It's a pleasure.

--

HTH

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

"Aaron Fude" wrote in message
...
Nice. Thank you.

"Bob Phillips" wrote in message
...
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)

"Aaron Fude" wrote in message
...
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