Creating toolbars thru VBA

[Guest]

Hi

Is it possible to create a tool bar thru VB code? and check if it exists for
that user, if it dosen't then create it. I want all users of a shared spread
sheet to get the same toolbar. I don't want to over write the XLB file below
as suggested below, in case it overwrites there current toolbar seetings..





Some one suggests



Menu and Toolbar customizations are stored in your *.XLB file.

Version 97 is username.xlb and found in C:\Windows folder.

Versions 2000 through 2003 are Excel9, Excel10 and Excel11.xlb and would be
found in

Documents and Settings\yourname\application data\microsoft\Excel

One method is to send your *.XLB file to the other user and have that user
overwrite their own *.xlb with yours.

Another method is to Attach the Toolbar(Tools>Customize>Toolbars) to a file
and send that file.


Gord Dibben Excel MVP

 

[Dave Peterson]

See your other post.

Hi

Is it possible to create a tool bar thru VB code? and check if it exists for
that user, if it dosen't then create it. I want all users of a shared spread
sheet to get the same toolbar. I don't want to over write the XLB file below
as suggested below, in case it overwrites there current toolbar seetings..

Some one suggests

Menu and Toolbar customizations are stored in your *.XLB file.

Version 97 is username.xlb and found in C:\Windows folder.

Versions 2000 through 2003 are Excel9, Excel10 and Excel11.xlb and would be
found in

Documents and Settings\yourname\application data\microsoft\Excel

One method is to send your *.XLB file to the other user and have that user
overwrite their own *.xlb with yours.

Another method is to Attach the Toolbar(Tools>Customize>Toolbars) to a file
and send that file.

Gord Dibben Excel MVP

 

[Bob Phillips]

I wouldn't bother checking if it exist, just trash it and re-create it.

Sub myToolbar()
Dim oCB As Object
Dim oCtl As CommandBarControl
Dim newMenu As CommandBarControl
Dim ctrlButton As CommandBarControl

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

Set oCB = Application.CommandBars.Add( _
Name:="MyCB", temporary:="True")

With oCB
With .Controls.Add(Type:=msoControlButton)
.Caption = "Button1"
.Style = msoButtonIcon
.FaceId = 29
.OnAction = "myMacro1"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Button2"
.Style = msoButtonIcon
.FaceId = 30
.OnAction = "myMacro2"
End With
.Visible = True
.Position = msoBarTop
End With

End Sub