Toolbar...

[bopo]

Hi

Basically I want to create a custom toolbar, I want the toolbar to feature several tools already provided in Excel such as sort (dont ask why). This is very easy to do via using the kind or wizard.

However this program will be used via other computers, therefore i want this toolbar to appear when a user opens this program. Im guessing I need to someone embed the toolbar using VBA, how could I implement this?

[lucas]

This will create a temporary floating toolbar that as set up will call 2 different macro's which I didn't include....but they are the onaction calls.
Sub AddNewCB()
'************************************************* ***************
' This procedure creates a new toolbar.
'************************************************* ***************
Dim CBar As CommandBar, CBarCtl As CommandBarControl
On Error GoTo AddNewCB_Err
' Create a new floating toolbar and make it visible.
On Error Resume Next '//EDIT (works better)
CommandBars("Utilities").Delete
Set CBar = CommandBars.Add(Name:="Utilities", Position:= _
msoBarFloating, Temporary:=True)
CBar.Visible = True
' Create a button with text on the bar and set some properties.
Set CBarCtl = CBar.Controls.Add(Type:=msoControlButton)
With CBarCtl
.Caption = "Navigator"
.Style = msoButtonCaption
.TooltipText = "Show Navigator"
.OnAction = "showNavform"
End With
' Create a button with an image on the bar and set some
' properties.
Set CBarCtl = CBar.Controls.Add(Type:=msoControlButton)
With CBarCtl
.FaceId = 1000
.Caption = "Icon Button"
.TooltipText = "Display Message Box"
.OnAction = "Message"
End With
Exit Sub
AddNewCB_Err:
MsgBox "Error " & Err.Number & vbCr & Err.Description
Exit Sub
End Sub

[lucas]

Put the above in a standard module and put the following in the ThisWorkbook module to run it when the workbook it opened.
Private Sub Workbook_Open()
AddNewCB
End Sub

[johnske]

BTW bopo,

As you are using inbuilt controls on your toolbar and you already know how to use the toolbar 'wizard' you can record all of those actions using the macro recorder and make your own procedure :) e.g. this is an example recorded macro...
'recorded macro
Sub Macro1()
Application.CommandBars.Add(Name:="Custom 1").Visible = True
Application.CommandBars("Custom 1").Controls.Add Type:=msoControlButton, ID _
:=928, Before:=1
Application.CommandBars("Custom 1").Controls.Add Type:=msoControlButton, ID _
:=210, Before:=2
End Sub
Now modify the recorded macro so that it's optimized and reflects your preferred options, e.g. Temporary, which it should always be if this is to go on someone elses PC (or else call a procedure to delete the toolbar with a BeforeClose event)

'copy and rename recorded macro then put in
'checks/balances & optimize code
Sub AddMyToolbar()
Call RemoveMyToolbar '< remove any previous instances
With Application
.CommandBars.Add(Name:="Custom 1", Temporary:=True).Visible = True
With .CommandBars("Custom 1").Controls
.Add Type:=msoControlButton, ID:=928, Before:=1
.Add Type:=msoControlButton, ID:=210, Before:=2
End With
End With
End Sub

Add a procedure to remove any instances that may be inadvertantly left hanging around (This could also be called from a BeforeClose event)...
'remove the added toolbar
Sub RemoveMyToolbar()
On Error Resume Next
Application.CommandBars("Custom 1").Delete
End Sub