Solved: assign macro to toolbar button using code

[Tezzies]

I Know this can be done manually, is there code for doing this. ????

[Bob Phillips]

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("myToolbar").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim oCtl As CommandBarControl

On Error Resume Next
Application.CommandBars("myToolbar").Delete
On Error GoTo 0

Set oCB = Application.CommandBars.Add(Name:="myToolbar", temporary:=True)
With oCB
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.BeginGroup = True
.Caption = "savenv"
.OnAction = "savenv"
.FaceId = 27
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "savemyprog"
.OnAction = "savemyprog"
.FaceId = 28
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "macro4"
.OnAction = "macro4"
.FaceId = 29
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "dater"
.OnAction = "dater"
.FaceId = 30
End With
.Visible = True
.Position = msoBarTop
End With

End Sub


To add this, go to the VB IDE (ALT-F11 from Excel), and in
the explorer pane, select your workbook. Then select the
ThisWorkbook object (it's in Microsoft Excel Objects which
might need expanding). Double-click the ThisWorkbook and
a code window will open up. Copy this code into there,
changing the caption and action to suit.

This is part of the workbook, and will only exist with the
workbook, but will be available to anyone who opens the
workbook.

[lucas]

This should get you started...

http://vbaexpress.com/kb/getarticle.php?kb_id=921&PHPSESSID=40d1cc7a0ea815c21640b6b74c7f438a

[Tezzies]

:thumb Great stuff, got my button working.

How do I add text to the button though.?

Plus I was wondering if its possible to remember what toolbars where visible when you opened excel ,close them all bar my new shiny button then "on close" re-show the toolbars you closed .

Many thanks.

[Bob Phillips]

Why do you want text on a Toolbar?



Option Explicit

Private mFormulaBar

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB

Application.DisplayFormulaBar = mFormulaBar
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = False
Next oCB

mFormulaBar = Application.DisplayFormulaBar
Application.DisplayFormulaBar = False
End Sub

[Tezzies]

u is a frikkin genius with that code. last bit saved me alot of work and i am forever in your debt:friends: .

I need the button to say "save and close" . at the moment i can only change it to a symbol. the end product will be a blank excel document with my "save and close button" at the top. stops my users pressing daft buttons.

even if this cant be done many thanks again.

[Bob Phillips]

Instead of code like this



With oCtl
.Caption = "savemyprog"
.OnAction = "savemyprog"
.FaceId = 28
End With


use something like



With oCtl
.Caption = "save and close"
.Style = msoButtonCaption
.OnAction = "saveandclose"
End With

[Tezzies]

Thanks, works great.

Last question I promise . How do I check if a command bar exists ????

[Bob Phillips]

Dim cb As CommandBar

On Error Resume Next
Set cb = Application.CommandBars("commandbar_name")
On Error GoTo 0
If cb Is Nothing Then
MsgBox "No such commandbar"
End If

[Tezzies]

Thanks for all the Help XLD.

Everything works great.:hi: :hi: :hi: :hi: