MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Can I add a button from XLStart\Personal.xls?


Posted by David Megnin on May 11, 2001 11:11 AM

I have a macro I've attached to a custom button in Excel, but I want to distribute this macro to co-workers.

I would like the macro to add the button to Excel on their PCs so I don't have to go around to everyones desk and do it manually. Is this possible?

Thanks in advance!!
David


Posted by Paul E. on May 11, 2001 11:32 AM

Hi David,
I've been working on the same thing. One suggestion that I have, would be to save the macro as an *.xla (add-in file), and have your associates save it in their Microsoft Office Library directory. That way they can select the add-in from the addins dialog box, and load it into Excel, when needed. But first you need to create a toolbar (containing a button referencing your macro) that automatically loads when this add-in is selected.

Below are two subroutines that you can put into your VBA code and reference from the Workbooks_Open and Workbook_BeforeClose subs. Calling the first subroutine from the Workbook_Open sub in your *.xla file, will automatically create the toolbar and a button which references the macro you which to execute. The second subroutine erases the toolbar when the add-in is deselected fromt he add-in dialog box.

Call this subroutine from the Workbooks_Open sub
'''''''''''''''''''''''''''''''''''''''
Private Const CBR_INSERT As String = "Place your toolbar name here"
Private Const CTL_INSERT As String = "Place a name for the button here"

Sub Exec()
Dim cbrWiz As CommandBar
Dim ctlInsert As CommandBarButton
On Error Resume Next
' Determine whether command bar already exists.
Set cbrWiz = CommandBars(CBR_INSERT)
' If command bar does not exist, create it.
If cbrWiz Is Nothing Then
err.Clear
Set cbrWiz = CommandBars.Add(CBR_INSERT)
' Make command bar visible.
cbrWiz.Visible = True
' Add button control.
Set ctlInsert = cbrWiz.Controls.Add
With ctlInsert
.Style = msoButtonCaption
.Caption = CTL_INSERT
.Tag = CTL_INSERT
' Specify procedure that will run when button is clicked.
.OnAction = "Place name of macro subroutine here" (i.e. "Execute")
End With
End If
End Sub


Call this subroutine from the Workbook_BeforeClose sub
''''''''''''''''''''''''''''''''''''''''''
Sub ext()
On Error Resume Next
' Delete command bar, if it exists.
CommandBars(CBR_INSERT).Delete
End Sub


Good Luck!!

Paul

Posted by David Megnin on May 11, 2001 12:56 PM

Thanks a lot Paul, I'll give it a try.