Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros on Excel Custom Toolbars.
I am using Excel 97.
I have written some temples in Excel which utilise some code in VBA to complete some of the fields, these all work fine. I also have a custom toolbar with buttons which call macros in the same template. For some reason the code behind the buttons don't stay with the template. When I create a spreadsheet from one of these templates, the path of the code changes and the code can't be found. Any solutions would be appreciated. Mark |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros on Excel Custom Toolbars.
Mark,
I would not attach a toolbar to a template, the fact that you create many copies from that template can only complicate matters. I would create the toolbars from a one instance workbook, such as the Personal.xls workbook, or from within an addin. Furthermore, I always create my toolbars from code, not manually. Here is a simple example that creates a toolbar, adds various buttons, with icons and text, and positions the toolbar. This code would normally go in a workbook open event in Thisworkbook On Error Resume Next Application.CommandBars(appMenu).Delete On Error GoTo 0 Set oCB = Application.CommandBars.Add(Name:=appMenu, temporary:=True) With oCB With .Controls.Add(Type:=msoControlButton) .Caption = appMenu & " Toolbar" .Style = msoButtonCaption End With With .Controls.Add(Type:=msoControlButton) .BeginGroup = True .Caption = "Open File" .FaceId = 23 .Style = msoButtonIconAndCaption .OnAction = "OpenFiles" End With With .Controls.Add(Type:=msoControlButton) .BeginGroup = True .Caption = "Sort Results" .FaceId = 210 .Style = msoButtonIconAndCaption .OnAction = "BCCCSort" End With With .Controls.Add(Type:=msoControlButton) .BeginGroup = True .Caption = "New Player" .FaceId = 316 .Style = msoButtonIconAndCaption .OnAction = "NewEntry" End With With .Controls.Add(Type:=msoControlDropdown) .BeginGroup = True .Caption = "Delete" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Delete " .Style = msoButtonCaption .OnAction = "RemoveEntry " .Parameter = "Toolbar" End With With .Controls.Add(Type:=msoControlButton) .BeginGroup = True .Caption = "New Sheet" .FaceId = 18 .Style = msoButtonIconAndCaption .OnAction = "NewSheet" End With With .Controls.Add(Type:=msoControlButton) .BeginGroup = True .Caption = "New Workbook" .FaceId = 245 .Style = msoButtonIconAndCaption .OnAction = "NewBook" End With With .Controls.Add(Type:=msoControlButton) .BeginGroup = True .Caption = "About..." .FaceId = 941 .Style = msoButtonIconAndCaption .OnAction = "About" End With .Visible = True .Position = msoBarTop End With To get a utility to see what FaceIds are available, visit JOhn Walkenbach's site at http://j-walk.com/ss/excel/tips/tip67.htm -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Mark" wrote in message ... I am using Excel 97. I have written some temples in Excel which utilise some code in VBA to complete some of the fields, these all work fine. I also have a custom toolbar with buttons which call macros in the same template. For some reason the code behind the buttons don't stay with the template. When I create a spreadsheet from one of these templates, the path of the code changes and the code can't be found. Any solutions would be appreciated. Mark |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros on Excel Custom Toolbars.
Thanks Bob,
Works much better, I'm afraid I a bit of a novice so can you answer me a few questions, please? How do you changed the toolbar name from Custom to something more useful? Is there a way of displaying the buttons downward, rather than across, and finally! How is the toolbar deleted when the spreadsheet is closed? Mark -----Original Message----- Mark, I would not attach a toolbar to a template, the fact that you create many copies from that template can only complicate matters. I would create the toolbars from a one instance workbook, such as the Personal.xls workbook, or from within an addin. Furthermore, I always create my toolbars from code, not manually. Here is a simple example that creates a toolbar, adds various buttons, with icons and text, and positions the toolbar. This code would normally go in a workbook open event in Thisworkbook On Error Resume Next Application.CommandBars(appMenu).Delete On Error GoTo 0 Set oCB = Application.CommandBars.Add(Name:=appMenu, temporary:=True) With oCB With .Controls.Add(Type:=msoControlButton) .Caption = appMenu & " Toolbar" .Style = msoButtonCaption End With With .Controls.Add(Type:=msoControlButton) .BeginGroup = True .Caption = "Open File" .FaceId = 23 .Style = msoButtonIconAndCaption .OnAction = "OpenFiles" End With With .Controls.Add(Type:=msoControlButton) .BeginGroup = True .Caption = "Sort Results" .FaceId = 210 .Style = msoButtonIconAndCaption .OnAction = "BCCCSort" End With With .Controls.Add(Type:=msoControlButton) .BeginGroup = True .Caption = "New Player" .FaceId = 316 .Style = msoButtonIconAndCaption .OnAction = "NewEntry" End With With .Controls.Add(Type:=msoControlDropdown) .BeginGroup = True .Caption = "Delete" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Delete " .Style = msoButtonCaption .OnAction = "RemoveEntry " .Parameter = "Toolbar" End With With .Controls.Add(Type:=msoControlButton) .BeginGroup = True .Caption = "New Sheet" .FaceId = 18 .Style = msoButtonIconAndCaption .OnAction = "NewSheet" End With With .Controls.Add(Type:=msoControlButton) .BeginGroup = True .Caption = "New Workbook" .FaceId = 245 .Style = msoButtonIconAndCaption .OnAction = "NewBook" End With With .Controls.Add(Type:=msoControlButton) .BeginGroup = True .Caption = "About..." .FaceId = 941 .Style = msoButtonIconAndCaption .OnAction = "About" End With .Visible = True .Position = msoBarTop End With To get a utility to see what FaceIds are available, visit JOhn Walkenbach's site at http://j-walk.com/ss/excel/tips/tip67.htm -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Mark" wrote in message ... I am using Excel 97. I have written some temples in Excel which utilise some code in VBA to complete some of the fields, these all work fine. I also have a custom toolbar with buttons which call macros in the same template. For some reason the code behind the buttons don't stay with the template. When I create a spreadsheet from one of these templates, the path of the code changes and the code can't be found. Any solutions would be appreciated. Mark . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do we get 2003 .xlb toolbars and custom macros to show in 07 | Excel Discussion (Misc queries) | |||
Excel Toolbars and Macros in Vista | Setting up and Configuration of Excel | |||
Custom Excel Toolbars - how? | Excel Discussion (Misc queries) | |||
Saving custom toolbars with macros to work on other computers | Excel Discussion (Misc queries) | |||
Excel 2002 custom toolbars | Excel Discussion (Misc queries) |