Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: [VBA] manually-select ActiveX commandbuttons & change font size
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2015
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default [VBA] manually-select ActiveX commandbuttons & change font size

    I need to manually select certain ActiveX commandbuttons on a worksheet, and then change its font size.
    Below is my attempted code but returns error.

    Code:
    Sub Testing()
        Dim o As Variant
        For Each o In Selection
            o.Font.Size = 20
        Next
    End Sub

    Thanks a lot!

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,867
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: [VBA] manually-select ActiveX commandbuttons & change font size

    You cannot do what you are trying to do

    But you can loop all command buttons in the worksheet and achieve the same thing ...
    - message box returns caption of each command button
    - reply with yes or no to each message
    - update to font size occurs when the code stops running

    Code:
    Sub ChangeFont()
        Dim objX As Object
        With ActiveSheet
            For Each objX In .OLEObjects
                If TypeName(objX.Object) = "CommandButton" Then
                    If MsgBox(objX.Object.Caption, vbYesNo, "Amend this one?") = vbYes Then objX.Object.Font.Size = 20
                End If
            Next
        End With
    End Sub

  3. #3
    Board Regular
    Join Date
    Jul 2015
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VBA] manually-select ActiveX commandbuttons & change font size

    Thank you Yongle!
    By the way, I found a 1-by-1 method.
    Sub Testing()
    Selection.Font.Size = 20
    End Sub
    Last edited by smallxyz; Aug 20th, 2019 at 05:37 AM.

  4. #4
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,867
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: [VBA] manually-select ActiveX commandbuttons & change font size

    By the way, I found a 1-by-1 method.
    Sub Testing()
    Selection.Font.Size = 20
    End Sub
    I am intrigued - how are you making that work ?
    Active-X command button cannot be selected unless user switches to Design Mode but macros are disabled when in Design Mode

  5. #5
    Board Regular
    Join Date
    Jul 2015
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VBA] manually-select ActiveX commandbuttons & change font size

    I attach the sub-routine as a commandbutton on an Userform first, then access the userform by assigning it as the first quick-tool, using Alt + 1.

  6. #6
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,867
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: [VBA] manually-select ActiveX commandbuttons & change font size

    but how are you selecting an active-x command button on the worksheet ?

  7. #7
    Board Regular
    Join Date
    Jul 2015
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VBA] manually-select ActiveX commandbuttons & change font size

    Using Design Mode. After selection, I will trigger the userform using Alt + 1.

  8. #8
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,867
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: [VBA] manually-select ActiveX commandbuttons & change font size

    I am obviously missing a trick here

    {ALT} 1 code: UserForm1.Show
    The command button on userform code: Selection.Font.Size = 20

    in Design Mode BEFORE opening the userform, the sheet active-x command button can be selected BUT ...

    ALT + 1 brings up the userform (no surprises there)

    which immediately deselects the command button

    And after that I cannot select an active-x command button on the worksheet without closing the userform


    What are you doing that is different ?

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,981
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: [VBA] manually-select ActiveX commandbuttons & change font size

    Sounds like it's a Forms control button rather than an ActiveX one.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,867
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: [VBA] manually-select ActiveX commandbuttons & change font size


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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