Modify Buttons with VBA (XL2000 SR3)

Topic

New Reply

I can add a toolbar to a workbook and add one or more buttons to the toolbar by going to Customise and dragging the yellow smiley onto the toolbar, using the macro recorder to give me code for these actions. However when I change the style of the button to text and image, alter the button name, alter the button image, or assign a macro to the button, no code appears in the recorded macro. How does one do these things by code?

Reply | Quote

Replies

You need to write such code from scratch. In this case, you need to modify a CommandBarButton object; here is an example:

Sub ModifyButton()
Dim ctl As CommandBarButton
Set ctl = CommandBars("Standard").Controls("F2V")
With ctl
.Style = msoButtonIconAndCaption
.Caption = "Formulas to Values"
.FaceId = 37
.OnAction = "MyMacro"
End With
End Sub

Click in a word such as Style or FaceId in the Visual Basic Editor and press F1 to get help.

Reply | Quote

(Edited by HansV to make URL clickable – see Help 19)

Thank you Hans

I could not find a listing of FaceId’s in help, however this site has them http://www.j-walk.com/ss/excel/tips/tip67.htm%5B/url%5D

Reply | Quote