AutoLoad Custom Tool Bar
Can anyone help me? I am trying to get a custom toolbar to load when I load
a specific workbook in Excel. The custom toolbar is designed and working, but I want to autoload it when I load the specific workbook that it is used for. What are the VB code lines that will do this? Also I would like to know how to close (exit) the tool bar when I close the specific workbook. Thank you for your help. Jim |
AutoLoad Custom Tool Bar
Jim,
Here is a simple example. Put the code in Thisworkbook Dim sToolbar As String Private Sub Workbook_BeforeClose(Cancel As Boolean) sToolbar = "MyToolbar" On Error Resume Next Application.CommandBars("Formatting").Controls(sTo olbar).Delete On Error GoTo 0 End Sub Private Sub Workbook_Open() Dim oCB As CommandBar Dim oCtl As CommandBarControl Dim newMenu As Object 'CommandBarControl Dim ctrlButton As Object 'CommandBarControl sToolbar = "MyToolbar" On Error Resume Next Application.CommandBars(sToolbar).Delete On Error GoTo 0 Set oCB = Application.CommandBars.Add(temporary:=True) With oCB .Name = sToolbar With .Controls.Add(Type:=msoControlButton, temporary:=True) .BeginGroup = True .Caption = "Margin Calculator" .FaceId = 197 .Style = msoButtonIconAndCaption .OnAction = "myMacro" End With .Visible = True End With End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Jim Carter" wrote in message ... Can anyone help me? I am trying to get a custom toolbar to load when I load a specific workbook in Excel. The custom toolbar is designed and working, but I want to autoload it when I load the specific workbook that it is used for. What are the VB code lines that will do this? Also I would like to know how to close (exit) the tool bar when I close the specific workbook. Thank you for your help. Jim |
AutoLoad Custom Tool Bar
Jim, the easiest solution is to attach your toolbar to the workbook via
View, Toolbars, Customize. Then, when you close the workbook, use the following macro that runs when a workbook is closed: Sub Auto_Close() commandbars("my toolbar name").delete End Sub You could hide the toolbar instead of deleting, but you would need to then use a statement in the subrouting auto_open to unhide it. For example commandbars("my toolbar name").visible = true or false Robert Flanagan Macro Systems Delaware, U.S. 302-234-9857 http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Jim Carter" wrote in message ... Can anyone help me? I am trying to get a custom toolbar to load when I load a specific workbook in Excel. The custom toolbar is designed and working, but I want to autoload it when I load the specific workbook that it is used for. What are the VB code lines that will do this? Also I would like to know how to close (exit) the tool bar when I close the specific workbook. Thank you for your help. Jim |
AutoLoad Custom Tool Bar
Jim
Try this KB Article. http://msdn.microsoft.com/library/de...l/ofcmdbar.asp If no happiness, post back. Gord Dibben Excel MVP On Mon, 16 Aug 2004 15:44:45 -0600, "Jim Carter" wrote: Can anyone help me? I am trying to get a custom toolbar to load when I load a specific workbook in Excel. The custom toolbar is designed and working, but I want to autoload it when I load the specific workbook that it is used for. What are the VB code lines that will do this? Also I would like to know how to close (exit) the tool bar when I close the specific workbook. Thank you for your help. Jim |
All times are GMT +1. The time now is 05:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com