How do I add a vertical spacer bar in toolbar
I have some VBA code that creates a new toolbar when I open the
spreadsheet. The toolbar has two buttons on it which are labelled with words rather than an icon. The two buttons run other macros. I would like to include some additional code that will place a vertical spacer bar between the two buttons so that they look more separated on the screen. I have tried recording a macro whilst doing the process manually but the macro had no code in it when I finished. Can anybody tell me what the code is that I should use? I am using XL97 on Win NT 4. The code that creates the toolbar is as follows: Sub MakeToolBar() Dim Ctl As CommandBarControl On Error Resume Next Application.CommandBars("Sorting projects").Delete On Error GoTo 0 'Create the toolbar Application.CommandBars.Add ("Sorting projects") 'Add the first button Set Ctl = Application.CommandBars("Sorting projects").Controls.Add With Ctl .Style = msoButtonCaption .Width = 112 .Caption = "Subtotal by Funding Source" .TooltipText = "Sort and subtotal by Funding Source" .OnAction = "Subtotal_By_Funder" End With 'Add the second button Set Ctl = Application.CommandBars("Sorting projects").Controls.Add With Ctl .Style = msoButtonCaption .Width = 112 .Caption = "Subtotal by Work Stage" .TooltipText = "Sort and subtotal by Work Stage" .OnAction = "Subtotal_By_Stage" End With 'Make toolbar visible Application.CommandBars("Sorting projects").Visible = True End Sub |
How do I add a vertical spacer bar in toolbar
Hi Graham,
For your 2nd Button you have to set it's BeginGroup Property to True. ctl.BeginGroup = True Regards, Shah Shailesh http://members.lycos.co.uk/shahweb/ (Excel Add-ins) *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
How do I add a vertical spacer bar in toolbar
Hi Graham,
Add the following within the second Ctl With statement ..BeginGroup = True Graham Standring wrote: I have some VBA code that creates a new toolbar when I open the spreadsheet. The toolbar has two buttons on it which are labelled with words rather than an icon. The two buttons run other macros. I would like to include some additional code that will place a vertical spacer bar between the two buttons so that they look more separated on the screen. I have tried recording a macro whilst doing the process manually but the macro had no code in it when I finished. Can anybody tell me what the code is that I should use? I am using XL97 on Win NT 4. The code that creates the toolbar is as follows: Sub MakeToolBar() Dim Ctl As CommandBarControl On Error Resume Next Application.CommandBars("Sorting projects").Delete On Error GoTo 0 'Create the toolbar Application.CommandBars.Add ("Sorting projects") 'Add the first button Set Ctl = Application.CommandBars("Sorting projects").Controls.Add With Ctl .Style = msoButtonCaption .Width = 112 .Caption = "Subtotal by Funding Source" .TooltipText = "Sort and subtotal by Funding Source" .OnAction = "Subtotal_By_Funder" End With 'Add the second button Set Ctl = Application.CommandBars("Sorting projects").Controls.Add With Ctl .Style = msoButtonCaption .Width = 112 .Caption = "Subtotal by Work Stage" .TooltipText = "Sort and subtotal by Work Stage" .OnAction = "Subtotal_By_Stage" End With 'Make toolbar visible Application.CommandBars("Sorting projects").Visible = True End Sub -- Cheers Andy http://www.andypope.info |
How do I add a vertical spacer bar in toolbar
Thanks to both Andy and Shailesh.
Placing the .BeginGroup command within the second "With Ctl" statement did not work but through trial and error, and of course having been made aware by you of that command, I managed to make it work by putting the following line after the second "With Ctl" statement. Application.CommandBars("Sorting projects").Controls(2).BeginGroup = True Thanks for your help. Graham Andy Pope wrote: Hi Graham, Add the following within the second Ctl With statement .BeginGroup = True Graham Standring wrote: I have some VBA code that creates a new toolbar when I open the spreadsheet. The toolbar has two buttons on it which are labelled with words rather than an icon. The two buttons run other macros. I would like to include some additional code that will place a vertical spacer bar between the two buttons so that they look more separated on the screen. I have tried recording a macro whilst doing the process manually but the macro had no code in it when I finished. Can anybody tell me what the code is that I should use? I am using XL97 on Win NT 4. The code that creates the toolbar is as follows: Sub MakeToolBar() Dim Ctl As CommandBarControl On Error Resume Next Application.CommandBars("Sorting projects").Delete On Error GoTo 0 'Create the toolbar Application.CommandBars.Add ("Sorting projects") 'Add the first button Set Ctl = Application.CommandBars("Sorting projects").Controls.Add With Ctl .Style = msoButtonCaption .Width = 112 .Caption = "Subtotal by Funding Source" .TooltipText = "Sort and subtotal by Funding Source" .OnAction = "Subtotal_By_Funder" End With 'Add the second button Set Ctl = Application.CommandBars("Sorting projects").Controls.Add With Ctl .Style = msoButtonCaption .Width = 112 .Caption = "Subtotal by Work Stage" .TooltipText = "Sort and subtotal by Work Stage" .OnAction = "Subtotal_By_Stage" End With 'Make toolbar visible Application.CommandBars("Sorting projects").Visible = True End Sub |
All times are GMT +1. The time now is 03:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com