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

smallxyz

Active Member
Joined
Jul 27, 2015
Messages
392
Office Version
  1. 2021
Platform
  1. Windows
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!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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
 
Upvote 0
Thank you Yongle!
By the way, I found a 1-by-1 method.
Sub Testing()
Selection.Font.Size = 20
End Sub
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
but how are you selecting an active-x command button on the worksheet ?
 
Upvote 0
Using Design Mode. After selection, I will trigger the userform using Alt + 1.
 
Upvote 0
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 ?
 
Upvote 0
Sounds like it's a Forms control button rather than an ActiveX one.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top