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

[smallxyz]

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.

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

Thanks a lot!

 

[Yongle]

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

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

 

[smallxyz]

Thank you Yongle!
By the way, I found a 1-by-1 method.
Sub Testing()
Selection.Font.Size = 20
End Sub

 

[Yongle]

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

 

[smallxyz]

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.

 

[Yongle]

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

 

[smallxyz]

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

 

[Yongle]

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 ?

 

[Fluff]

Sounds like it's a Forms control button rather than an ActiveX one.

 

[Yongle]

@Fluff