Hi All

I have written a macro function that works well for me.

I now want to set up a custom button and associate it to the function such
that I can distribute an add in with the customised button mapped to that
function.

Is that possible?
TIA
Andy

Andy,

Here's some code to create a toolbar on workbook open, and delete it on
close.

Private Sub Workbook_Open()
Dim oCb As CommandBar
Dim oCtl As CommandBarPopup
Dim oCtlBtn As CommandBarButton

On Error Resume Next
Application.CommandBars("Custom Toolbar").Delete ' just in case
On Error GoTo 0
Set oCb = Application.CommandBars.Add(Name:="Custom Toolbar")
With oCb
Set oCtlBtn = .Controls.Add(Type:=msoControlButton,
temporary:=True)
oCtlBtn.Caption = "myMacroButton"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro"
.Visible = True
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCb As CommandBar

Set oCb = Application.CommandBars("Custom Toolbar")
oCb.Controls("myMacroButton").Delete
End Sub


Put the code in the ThisWorkbook code module

--

HTH

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

"Andy" wrote in message
...
Hi All

I have written a macro function that works well for me.

I now want to set up a custom button and associate it to the function such
that I can distribute an add in with the customised button mapped to that
function.

Is that possible?
TIA
Andy

Bob gave you some good sample code. In addition it might be worth your
while to actually learn a little about this topic:

Here is an article which talks about attaching toolbars:

http://www.microsoft.com/exceldev/articles/toolbatt.htm

Here is an article about creating commandbars with code:
http://msdn.microsoft.com/library/techart/ofcmdbar.htm

Here is another article or two you might find useful:
http://msdn.microsoft.com/library/ba...n_addins97.htm
http://msdn.microsoft.com/library/of...exceladdin.htm
http://www.microsoft.com/exceldev/tips/addins.htm
These are about distributing applications

http://support.microsoft.com/?id=159619
XL97: Sample Macros for Customizing Menus and Submenus

http://support.microsoft.com/?id=213550
XL2000: Sample Macros for Customizing Menus and Submenus


http://support.microsoft.com/default...b;en-us;166755
File Title: Customizing Menu Bars, Menus, and Menu Items in Microsoft(R)
Excel 97
File Name: WE1183.EXE
File Size: 58041 bytes
File Date: 06/20/97
Keywords: kbfile kbappnote
Description: This Application Note can help you learn techniques for writing
Visual Basic(R) for Applications code to customize menus in Microsoft Excel
97. This Application Note contains code examples that you can use with the
following elements: menu bars, menus, menu items, submenus, and shortcut
menus.

--
Regards,
Tom Ogilvy



"Andy" wrote in message
...
Hi All

I have written a macro function that works well for me.

I now want to set up a custom button and associate it to the function such
that I can distribute an add in with the customised button mapped to that
function.

Is that possible?
TIA
Andy

Thank you both

I added the code to Auto_Open and it works well for me.

Many thanks
Andy

I forgot my usual bit about visiting John Walkenbach's webpage site at
http://j-walk.com/ss/excel/tips/tip67.htm to help find the values of
the FaceIds, which will give you a decent toolbar button image.

--

HTH

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

"Bob Phillips" wrote in message
...
Andy,

Here's some code to create a toolbar on workbook open, and delete it on
close.

Private Sub Workbook_Open()
Dim oCb As CommandBar
Dim oCtl As CommandBarPopup
Dim oCtlBtn As CommandBarButton

On Error Resume Next
Application.CommandBars("Custom Toolbar").Delete ' just in case
On Error GoTo 0
Set oCb = Application.CommandBars.Add(Name:="Custom Toolbar")
With oCb
Set oCtlBtn = .Controls.Add(Type:=msoControlButton,
temporary:=True)
oCtlBtn.Caption = "myMacroButton"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro"
.Visible = True
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCb As CommandBar

Set oCb = Application.CommandBars("Custom Toolbar")
oCb.Controls("myMacroButton").Delete
End Sub


Put the code in the ThisWorkbook code module

--

HTH

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

"Andy" wrote in message
...
Hi All

I have written a macro function that works well for me.

I now want to set up a custom button and associate it to the function
such
that I can distribute an add in with the customised button mapped to
that
function.

Is that possible?
TIA
Andy