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/DLCount.asp?file=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