Adding Macros to Toolbar

[theburst]

I've created several macros which I want to add to the a toolbar. I know how to add them to a toolbar on my computer, but the file that that work with is on a server and I'd like to add them to a toolbar on the file so that when any user opens it they are there ready to be used.

Thoughts?

 

[bdmangum]

Which toolbar are you wanting to add the macro to?

Below is the code I use for adding a button to the commandbar upon opening the file. It then removes it upon closing the file. Thus the button is only on the commandbar when that particular file is open.

Public mycontrol As Object

Private Sub Workbook_Open()

Set mycontrol = Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlButton, ID:=2950, Before:=11)
With mycontrol
    .Caption = "Run Macro"   'Sets the caption
    .FaceId = 2950      'Sets the icon
    .OnAction = "Macro"    'Set which macro will run
    .Style = msoButtonAutomatic   'Use either of the following to replace 'msoButtonAutomatic' for either caption only or icon only: 'msoButtonCaption 'msoButtonIcon
End With

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error GoTo finish
mycontrol.Delete

finish:

End Sub

 

[theburst]

Ya thats great, I hadn't though of running a macro to place the buttons for other macros!

I'm thinking about adding them to a menu now, because I have about nine. But this idea is just what I needed.

Its actually for Microsoft Project, which I had neglected to mention before.

Thanks!

 

[bdmangum]

Here's another way you could do this, if you want your own custom toolbar.

Private Sub Workbook_Open()

Dim NumMacros As Integer
Dim cbar As CommandBar
Dim myControl As Object

NumMacros = 1   'Number of macros to go in menu

'Checks to see if command bar exists
For Each cbar In CommandBars
    If cbar.Name = "Custom Menu" Then
        MsgBox "Custom Menu already exists."
        Exit Sub
    End If
Next

'Creates command bar
Set cbar = CommandBars.Add(Name:="Custom Menu", Position:=msoBarFloating)
cbar.Visible = True

'Loop this until you add all your macros to the new command bar
While NumMacros <> 0
    Set myControl = Application.CommandBars("Custom Menu").Controls.Add(Type:=msoControlButton, ID:=2950, Before:=1)
    With myControl
        .Caption = "Run Macro"   'Sets the caption
        .FaceId = 2950      'Sets the icon
        .OnAction = "Macro"    'Set which macro will run
        .Style = msoButtonAutomatic   'Use either of the following to replace 'msoButtonAutomatic' for either caption only or icon only: 'msoButtonCaption 'msoButtonIcon
    End With
    NumMacros = NumMacros - 1
Wend

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim cbar As CommandBar

'Deletes the custom command bar
For Each cbar In CommandBars
    If cbar.Name = "Custom Menu" Then
        cbar.Delete
    End If
Next

End Sub

BD

 

[bdmangum]

Whoops! I didn't mean to insert those commands in the Workbook events. Exchange the "Private Sub Workbook_Open" to something like "Sub Create_CmdBar". The same goes for the Workbook_BeforeClose. Something like "Kill_CmdBar" should do the job.

BD

 

[bdmangum]

Yet again I forgot to include a critical part of the code. The code below will start the macros upon opening/closing of the workbook. It will also hide/unhide the command bar based upon whether the workbook is active.

Sub Show_CmdBar()

Dim cbar As CommandBar

'Unhides the custom command bar
For Each cbar In CommandBars
    If cbar.Name = "Custom Menu" Then
        cbar.Visible = True
    End If
Next

End Sub

Sub Hide_CmdBar()

Dim cbar As CommandBar

'Hides the custom command bar
For Each cbar In CommandBars
    If cbar.Name = "Custom Menu" Then
        cbar.Visible = False
    End If
Next

End Sub


Private Sub Workbook_Activate()
    Show_CmdBar
End Sub

Private Sub Workbook_Deactivate()
    Hide_CmdBar
End Sub

Private Sub Workbook_Open()
    Create_CmdBar
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Kill_CmdBar
End Sub