Excel VBA Buttons - Need Code To Loop Buttons Collection and Change Font Size

Question

In Excel, I have 25 sheets with 1-2 buttons each. These are the buttons I get from the Developer Tab > Insert > Command Button (not activex) and assign to macros.

I need to loop through every button on every sheet and change the font size to 8 points and arial, can anyone give me the code to accomplish this? TIA

Answer 1

Hi headlydotcom,

In my code, replace:

                .FontStyle = "Arial"

with:

                   .Name = "Arial"

===

Regards,

Norman

Answer 2

Hi headlydotcom,

In Excel, I have 25 sheets with 1-2 buttons each. These are the buttons I get from the Developer Tab > Insert > Command Button (not activex) and assign to macros.

I need to loop through every button on every sheet and change the font size to 8 points and arial, can anyone give me the code to accomplish this? TIA

Try something like:

• Alt+F11 to open the VBA editor
• Alt+IM to insert a new code module
• In the new module, paste the following code

'=========>>

Option Explicit

'--------->>

Public Sub Tester()

    Dim WB As Workbook

    Dim SH As Worksheet

    Dim Btn As Button

    Set WB = ThisWorkbook

    For Each SH In WB.Worksheets

        For Each Btn In SH.Buttons

            With Btn.Font

                .Size = 8

                .FontStyle = "Arial"

            End With

        Next Btn

    Next SH

End Sub

'<<=========

• Alt+Q to close the VBA editor and return to Excel
• Save the file as a macro-enabled workbook with an xlsm extension
• Alt+F8 to open the macro window
• Select Tester | Run

===

Regards,

Norman