Loading and Deleting Customised Toolbars 1

[OldSkoolSkater]

Hi All,

I have an excel sheet that loads when I start excel. In this workbook, I would like to put some code into the Autpen event that would load a toolbar (or create it from scratch if necessary), that would contain buttons that run macros.

I would also like to put some additional code in to delete this toolbar when the workbook is closed.

Has anyone got any ideas on the best way to do this?

 

[ilses]

Check out the following MSDN article:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcel9/html/xltoolbatt.asp

I found it very helpful as a starter for working with custom toolbars.

IS

 

[OldSkoolSkater]

Thanks Ilses, but the reason I'd like to do this in code is because of the need to delete the toolbar from the workbook and reattach it from the default, each time I make a change, as this is for over 100 users.

Any additional help would be greatly appreciated.

 

[logius]

Well, I don't know if this'll help. In a project I'm working on, I had to create a custom dropdown menu for my application. I found the code at this website.

http://www.erlandsendata.no/english/vba/commandbars/menuxl97.htm

Maybe you can modify it to allow for a custom toolbar.

 

[dsi]

I haven't tested this little snipit, but it may be what you are looking for:

Add a toolbar button in Excel

Go to your Excel VB Editor and double click on the module "ThisWorkbook". The following code will make a button and bound it to a macro everytime. The button and toolbar will be temporary. If you want it to be permanent, change the boolean to False.

Private Sub Workbook_Open()
  Application.CommandBars.Add(Name:="Custom 1", _
    Position:=msoBarTop, Temporary:=True).Visible = True
  Application.CommandBars("Custom 1").Controls.Add _
    Type:=msoControlButton, Id:=2950, Before:=1
  Application.CommandBars("Custom 1").Controls.Item(1).OnAction = "Macro1"
  
  'To Delete the ToolBar
  'Application.CommandBars("Custom 1").Delete
End Sub

Hope this helps!