How do I add a vertical spacer bar in toolbar

[Graham Standring]

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

 

[Shailesh Shah]

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)

 

[Andy Pope]

Hi Graham,

Add the following within the second Ctl With statement

..BeginGroup = True

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

 

[Graham Standring]

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