I have a load of macros to compress data into a standard
form, and one macro to run them all sequentially.
I don't really want a button in the worksheet itself, so
I was wondering if there is any way of making a button in
a toolbar which will start the macro up?

Hi jon,

you can paste the following code into "This Workbook":

Private Sub Workbook_Open()

Dim cBar As CommandBar
Dim cCtl As Object
Dim nCtl As Object
Dim MnuPopup As CommandBarPopup
Dim BtnButton As CommandBarButton

For Each cCtl In Application.CommandBars("Worksheet
Menu Bar").Controls
If (cCtl.Caption = "Dead Stock Report") Then
Exit For
End If
Next cCtl
If (cCtl Is Nothing) Then
Set MnuPopup = Application.CommandBars("Worksheet
Menu Bar").Controls.Add(Type:=msoControlPopup,
Temporary:=True)
MnuPopup.Caption = "Dead Stock Report"
Set cCtl = MnuPopup
End If
For Each nCtl In cCtl.Controls
If ((nCtl.Caption = "Create Report From Raw Data")
Or _
(nCtl.Caption = "Merge New Retail Prices") Or _
(nCtl.Caption = "Create Store Workbooks") Or _
(nCtl.Caption = "Add Stock Differences")) _
Then
nCtl.Delete
End If
Next nCtl
Set BtnButton = cCtl.Controls.Add
(Type:=msoControlButton, Temporary:=True)
BtnButton.Caption = "Create Report From Raw Data"
BtnButton.OnAction = "MakeDeadStockReport"
Set BtnButton = cCtl.Controls.Add
(Type:=msoControlButton, Temporary:=True)
BtnButton.Caption = "Merge New Retail Prices"
BtnButton.OnAction = "MergeNewPrices"
Set BtnButton = cCtl.Controls.Add
(Type:=msoControlButton, Temporary:=True)
BtnButton.Caption = "Create Store Workbooks"
BtnButton.OnAction = "CreateStoreWorkbooks"
Set BtnButton = cCtl.Controls.Add
(Type:=msoControlButton, Temporary:=True)
BtnButton.Caption = "Add Stock Differences"
BtnButton.OnAction = "AddStockDifferences"

End Sub

The code will be activated when the workbook is open and
will add a PopUp labeled "Dead Stock Report" to the Excel
command bar. Then 4 buttons labeled "Create Report From
Raw Data", "Merge New Retail Prices", "Create Store
Workbooks", and "Add Stock Differences" are added to the
popup.

The OnAction property of each button must contain the name
of a public sub located within a module that will perform
the action.

Have fun...

Peter


-----Original Message-----
I have a load of macros to compress data into a standard
form, and one macro to run them all sequentially.
I don't really want a button in the worksheet itself, so
I was wondering if there is any way of making a button in
a toolbar which will start the macro up?

.