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

smallxyz

Active Member
Joined
Jul 27, 2015
Messages
368
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!
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,020
Office Version
365
Platform
Windows
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
 

smallxyz

Active Member
Joined
Jul 27, 2015
Messages
368
Thank you Yongle!
By the way, I found a 1-by-1 method.
Sub Testing()
Selection.Font.Size = 20
End Sub
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,020
Office Version
365
Platform
Windows
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
Joined
Jul 27, 2015
Messages
368
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,020
Office Version
365
Platform
Windows
but how are you selecting an active-x command button on the worksheet ?
 

smallxyz

Active Member
Joined
Jul 27, 2015
Messages
368
Using Design Mode. After selection, I will trigger the userform using Alt + 1.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,020
Office Version
365
Platform
Windows
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
Joined
Jun 12, 2014
Messages
40,684
Office Version
365
Platform
Windows
Sounds like it's a Forms control button rather than an ActiveX one.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,096
Messages
5,466,633
Members
406,493
Latest member
Hazem Hassan

This Week's Hot Topics

Top