I Know this can be done manually, is there code for doing this. ????
I Know this can be done manually, is there code for doing this. ????
[vba]
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
[/vba]
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.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
This should get you started...
http://vbaexpress.com/kb/getarticle....40b6b74c7f438a
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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.
Why do you want text on a Toolbar?
[vba]
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
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
u is a frikkin genius with that code. last bit saved me alot of work and i am forever in your debt .
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.
Instead of code like this
[vba]
With oCtl
.Caption = "savemyprog"
.OnAction = "savemyprog"
.FaceId = 28
End With
[/vba]
use something like
[vba]
With oCtl
.Caption = "save and close"
.Style = msoButtonCaption
.OnAction = "saveandclose"
End With
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Thanks, works great.
Last question I promise . How do I check if a command bar exists ????
[vba]
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
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Thanks for all the Help XLD.
Everything works great.