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

smallxyz

Active Member
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!
 

Yongle

Well-known Member
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
 

Yongle

Well-known Member
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 ?:confused:
Active-X command button cannot be selected unless user switches to Design Mode but macros are disabled when in Design Mode
 

smallxyz

Active Member
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.
 

smallxyz

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

Yongle

Well-known Member
I am obviously missing a trick here :confused:

{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

MrExcel MVP, Moderator
Sounds like it's a Forms control button rather than an ActiveX one.
 

Some videos you may like

This Week's Hot Topics

Top