Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Modify Buttons with VBA (XL2000 SR3)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Modify Buttons with VBA (XL2000 SR3)

    You need to write such code from scratch. In this case, you need to modify a CommandBarButton object; here is an example:
    <code>
    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
    </code>
    Click in a word such as Style or FaceId in the Visual Basic Editor and press F1 to get help.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify Buttons with VBA (XL2000 SR3)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •