Select standard toolbar button option

[Guest]

On the worksheet I can pull-down:
View > Toolbars > Customize
I can then right-click any of the standard toolbar buttons and select:
Image and Text

How can I do this with VBA?

 

[Guest]

Sure. Turn on the macro recorder while you do it manually or sample code.

.Style = msoButtonIconAndCaption

 

[Guest]

Code like this...

Sub test()
Dim cbr As CommandBar
Dim ctl As CommandBarControl

Set cbr = Application.CommandBars("Standard")
Set ctl = cbr.Controls("Open")
ctl.Style = msoButtonIconAndCaption
End Sub

or did you want the customize dialog

 

[Guest]

Thank you
--
Gary''s Student
gsnu200710

Sure. Turn on the macro recorder while you do it manually or sample code.

.Style = msoButtonIconAndCaption

 

[Guest]

Thank you.

I'll use it in this form. Don't need a dialog since I'll be doing it in
loops.

Thanks again.

 

[Guest]

Not that you need the loop code but...

Sub test()
Dim cbr As CommandBar
Dim ctl As CommandBarControl

Set cbr = Application.CommandBars("Standard")
For Each ctl In cbr.Controls
ctl.Style = msoButtonIconAndCaption
Next ctl
End Sub

 

[Tom Ogilvy]

Jim/Student,
I doubt this code will be run, but just a heads up, the Standard commandbar
has 3 types of controls:

CommandBarPopup &Paste
CommandBarButton &Format Painter
CommandBarComboBox &Undo

as examples. The CommandBarCombobox has a style property, but it doesn't
support msobuttonIconandCaption. The CommandBarPopup doesn't have a style
property.

So this code will produce an error after changing some of the buttons.

 

[Guest]

Thanks Tom... One way...
Sub test()
Dim cbr As CommandBar
Dim ctl As CommandBarControl

Set cbr = Application.CommandBars("Standard")
For Each ctl In cbr.Controls
With ctl
If .Type = msoControlButton Then .Style = msoButtonIconAndCaption
End With
Next ctl
End Sub

Or you could just use on error resume next with the previous code...