VBA Toolbar Ideas required

[moglione1]

I have to create a new TOOLBAR in Excel that is generated when a specifc spreadsheet is opened. E.g. When I open a TEST spreadsheet the code looks for TEST ID and then creates a toolbar with pre-defined macro buttons.

Does anybody know if this is possible. Any possible solutions will be hulgely and greatly received.

I know how to create the toolbar manually but now when the document is loaded

[dominicb]

Good morning moglione1

What you're asking here is actually pretty advanced stuff. The only way I can think of doing this is by creating a toolbar "on the fly" using VBA code and show using event procedures (Workbook_Open) and delete it before the file is closed using the Workbook_Close procedure.

The code to create a bar with one button is as follows:

Sub MyTB()
On Error Resume Next
Application.CommandBars("My Toolbar").Delete
Set TB = Application.CommandBars.Add(Name:="My Toolbar")
Set Btn = TB.Controls.Add(Type:=msoControlButton)
With Btn
.FaceId = 263
.OnAction = Macro1
End With
Application.CommandBars("My Toolbar").Visible = True
End Sub

When clicked the button will run Macro1. The image on the button is no. 263 (use the utility from the link below to see them all).

http://www.oaltd.co.uk/DLCount/DLCou...e=BtnFaces.zip

To delete your toolbar use this code:

Sub KillToolbar()
Application.CommandBars("My Toolbar").Delete
End Sub

That bit's nice and easy...!

HTH

DominicB

[dominicb]

Hi

Just had another thought. Why not build your toolbar, and then use a macro to unhide it when your workbook is open, and hide it away again when it closes. The toolbar will still be there, just hidden, so it won't be immediatley viewable.

HTH

DominicB