Re: Add a separator on a custom toolbar

[Rob Bovey]

Hi Jac,

Set:

Button.BeginGroup = True

for the button directly to the right of where you want the separator to
appear.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Hi everyone,
I want to add a separator between two buttons on a custom toolbar (between

the 3rd and the 4th). I cannot find out how to do it by code. It must be
very simple, like doing it by hand, but one has to know how (right click on
the menu, choose Personalize and move the button a slight bit to the right).

 

[Guest]

Hi again, Rob
I tried what you suggested in your writing but it does not work. I wander what I do wrong. Can you point it out to me, please? Note: this code is an old macro (Excel 95-97) that I adapted
'******************************************************************
Sub CreateBar(
On Error Resume Nex
Toolbars("Jac").Delet

Toolbars.Add Name:="Jac
Toolbars("Jac").Visible = Tru
With Applicatio
.ShowToolTips = Tru
.LargeButtons = Fals
.ColorButtons = Tru
End Wit

Toolbars("Jac").ToolbarButtons.Add Button:=177, Before:=
Toolbars("Jac").ToolbarButtons(1).OnAction = "FindVlookup
Toolbars("Jac").ToolbarButtons(1).Name =
"Find the next VLOOKUP formula

Toolbars("Jac").ToolbarButtons.Add Button:=130, Before:=
Toolbars("Jac").ToolbarButtons(2).OnAction = "GotoTable
Toolbars("Jac").ToolbarButtons(2).Name =
"Go to the address specified in the formula

Toolbars("Jac").ToolbarButtons.Add Button:=12, Before:=
Toolbars("Jac").ToolbarButtons(3).OnAction = "CutAndPaste
Toolbars("Jac").ToolbarButtons(3).Name =
"Cut and paste the table

Toolbars("Jac").ToolbarButtons.Add Button:=207, Before:=
Toolbars("Jac").ToolbarButtons(4).OnAction = "SaveTheFile
Toolbars("Jac").ToolbarButtons(4).Name =
"Save the file (Ctrl-s)

Dim MyBar As CommandBa
Dim MyControl As CommandBarContro

Set MyBar = Toolbars("Jac"
Set MyControl = MyBar.Controls(3
MyControl.BeginGroup = Tru

Toolbars("Jac").Width = 25
With Toolbars("Jac"
.Left = 15
.Top = 10
End Wit
End Su
'******************************************************************
Thank you again.

 

[Rob Bovey]

Hi Jac,

Here's your procedure converted to use the newer CommandBar objects.
Note that the numbers that identify the controls are different. If you want
to know what number identifies a given control, you can just check its Id
property in the Immediate window, e.g.

? CommandBars("Jac").Controls(1).Id

Sub CreateBar2()

Dim cbrBar As CommandBar
Dim ctlButton As CommandBarButton

On Error Resume Next
CommandBars("Jac").Delete
On Error GoTo 0

Set cbrBar = CommandBars.Add("Jac", msoBarFloating)
cbrBar.Visible = True

Set ctlButton = cbrBar.Controls.Add(msoControlButton)
ctlButton.FaceId = 140
ctlButton.OnAction = "FindVlookup"
ctlButton.TooltipText = _
"Find the next VLOOKUP formula"

Set ctlButton = cbrBar.Controls.Add(msoControlButton)
ctlButton.FaceId = 3159
ctlButton.OnAction = "GotoTable"
ctlButton.TooltipText = _
"Go to the address specified in the formula"

Set ctlButton = cbrBar.Controls.Add(msoControlButton)
ctlButton.FaceId = 21
ctlButton.BeginGroup = True
ctlButton.OnAction = "CutAndPaste"
ctlButton.TooltipText = _
"Cut and paste the table"

Set ctlButton = cbrBar.Controls.Add(msoControlButton)
ctlButton.FaceId = 271
ctlButton.OnAction = "SaveTheFile"
ctlButton.TooltipText = _
"Save the file (Ctrl-s)"

cbrBar.Width = 250
cbrBar.Left = 150
cbrBar.Top = 105

End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Hi again, Rob,
I tried what you suggested in your writing but it does not work. I wander

what I do wrong. Can you point it out to me, please? Note: this code is an
old macro (Excel 95-97) that I adapted.

 

[Guest]

Hi Rob
I am very impressed. It works! And it's modern code..
Thank you very much. I will use that version from now on
By the way, when you wrote
? CommandBars("Jac").Controls(1).Id, your probably meant
? CommandBars("Jac").Controls(1).FaceId, I am pretty sure of that
Thank you again and have nice day

 

[Rob Bovey]

Hi Jac,

Glad you got it to work. When reading the Id of a CommandBarControl, the
Id and FaceId properties are interchangeable. I just use Id because it's
shorter.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *