How to Change Font Size of the Whole Sheet with Excel VBA

In this article, I’ll show you how you can change the font size of a whole sheet with VBA in Excel. While working in Excel, we sometimes need to change the font size of a certain sheet for the sake of better visualization. Today you’ll learn to accomplish this with VBA with proper examples and illustrations.

Change Font Size of the Whole Sheet with Excel VBA (Quick View)

Sub Change_Font_Size_of_Single_Sheet()
Sheet_Name = "Sheet1"
Font_Size = 20
Worksheets(Sheet_Name).Cells.Font.Size = Font_Size
End Sub

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

How to Change Font Size of the Whole Sheet with Excel VBA (Step-by-Step Analysis)

Here I have got a worksheet called Sheet1 in a workbook. In Excel, the font size of a worksheet is set to 11 by default. We can inspect it by going to the Font section under the tab Home.

Our objective today is to change the font size of the worksheet using VBA in Excel.

1. Developing a Macro to Change the Font Size of a Single Whole Sheet with Excel VBA 

First, we’ll develop a Macro to change the font size of a single worksheet. We’ll learn the procedure through step-by-step analysis.

⧪ Step 1: Entering the Worksheet Name

First, we have to enter the worksheet name on which the font size will be changed. Here it’s Sheet1.

Sheet_Name = "Sheet1"

⧪ Step 2: Inserting the Font Size

Next, we’ll insert the new font size to which the font will be changed. Let’s change it to 20.

Font_Size = 20

⧪ Step 3: Changing the Font Size

This is the most important step. We’ll change the font size of the input worksheet to the given size.

Worksheets(Sheet_Name).Cells.Font.Size = Font_Size

Therefore, the complete VBA code will be:

⧭ VBA Code:

Sub Change_Font_Size_of_Single_Sheet()
Sheet_Name = "Sheet1"
Font_Size = 20
Worksheets(Sheet_Name).Cells.Font.Size = Font_Size
End Sub

⧭ Output:

Run this code. It’ll change the font size of the whole Sheet1 to 20.

Read More: Excel VBA: Change Font Color for Part of Text (3 Methods)

2. Creating a Macro to Change the Font Size of Multiple Sheets with Excel VBA

We’ve learned to change the font size of a single worksheet. Now we’ll develop a Macro to change the font sizes of multiple sheets.

⧪ Step 1: Entering the Worksheet Names

First of all, we’ll create an array of the worksheet names whose font sizes will be changed. Here we’ll change the font sizes of Sheet1 and Sheet2.

Dim Sheet_Names() As Variant

Sheet_Names = Array("Sheet1", "Sheet2")

⧪ Step 2: Entering the Font Size

Next, we’ll enter the font size. Here it’s again 20.

Font_Size = 20

⧪ Step 3: Changing the Font Size

This is the most important step. We’ll iterate through a for-loop to change the font sizes of the given worksheets.

For i = LBound(Sheet_Names) To UBound(Sheet_Names)
    Worksheets(Sheet_Names(i)).Cells.Font.Size = Font_Size
Next i

So, the complete VBA code will be:

⧭ VBA Code:

Sub Change_Font_Size_of_Multiple_Sheets()

Dim Sheet_Names() As Variant
Sheet_Names = Array("Sheet1", "Sheet2")

Font_Size = 20

For i = LBound(Sheet_Names) To UBound(Sheet_Names)
    Worksheets(Sheet_Names(i)).Cells.Font.Size = Font_Size
Next i

End Sub

⧭ Output:

Run this code. It’ll change the font sizes of Sheet1 and Sheet2 to 20.

Read More: How to Use VBA Code to Change Font Color in Excel (3 Methods)

Similar Readings

• How to Change Lowercase to Uppercase in Excel (6 Methods)
• How to Convert Text to Time Format with AM/PM in Excel (3 Methods)
• Excel VBA Codes to Bold Text in a String (5 Examples)
• How to Add Leading Zeros in Excel Text Format (10 Ways)
• How to Add Text after Number with Custom Format in Excel (4 Ways)

3. Perform a Macro to Change the Font Size of All the Sheets with Excel VBA

You can also develop a Macro to change the font sizes of all the worksheets. The VBA code will be:

⧭ VBA Code:

Sub Change_Font_Size_of_All_Sheets()

Font_Size = 20

For i = 1 To Sheets.Count
    Worksheets(i).Cells.Font.Size = Font_Size
Next i

End Sub

⧭ Output:

Run this code. It’ll change the font sizes of all the worksheets to 20.

Read More: [Fixed!] Unable to Change Font Color in Excel (3 Solutions)

4. Developing a UserForm to Change the Font Size of Single or Multiple Sheets

Finally, we’ll develop a UserForm to change the font sizes of a single worksheet or multiple worksheets.

⧪ Step 1: Opening a New UserForm

Go to the Insert > UserForm button in the Visual Basic Editor to enter a new UserForm.

⧪ Step 2: Dragging Tools to the UserForm

A new UserForm called UserForm1 will open. Drag 2 Labels, 2 ListBoxes, and 1 CommandButton to the UserForm as shown in the figure below.

Change the displays of Change Font Size of: and Font Size: respectively.

Also, change the display of the CommandButton to OK.

⧪ Step 3: Writing Code for CommanButton1

Double-click on CommandButton1 and insert this code for the private subprocedure called CommandButton1_Click.

Private Sub CommandButton1_Click()

On Error GoTo Message

For i = 1 To UserForm1.ListBox2.ListCount - 1
   If UserForm1.ListBox2.Selected(i) = True Then
       Font_Size = UserForm1.ListBox2.List(i)
       Exit For
    End If
Next i

For i = 0 To UserForm1.ListBox1.ListCount - 1
    If UserForm1.ListBox1.Selected(i) = True Then
        Worksheets(UserForm1.ListBox1.List(i)).Cells.Font.Size = Font_Size
    End If
Next i

Unload UserForm1

Exit Sub

Message:
    MsgBox "Choose One Font Size.", vbExclamation

End Sub

⧪ Step 4: Writing Code for Running the UserForm

In the end, insert a new Module and write this code for running the UserForm.

Sub Run_UserForm()

UserForm1.Caption = "Change Font Size"

UserForm1.ListBox1.ListStyle = fmListStyleOption
UserForm1.ListBox1.MultiSelect = fmMultiSelectMulti

UserForm1.ListBox2.ListStyle = fmListStyleOption

For i = 1 To Sheets.Count
    UserForm1.ListBox1.AddItem Worksheets(i).Name
Next i

For i = 0 To UserForm1.ListBox1.ListCount - 1
    If UserForm1.ListBox1.List(i) = ActiveSheet.Name Then
        UserForm1.ListBox1.Selected(i) = True
        Exit For
    End If
Next i

For i = 1 To 100
    UserForm1.ListBox2.AddItem i
Next i

Load UserForm1

UserForm1.Show

End Sub

⧪ Step 5: Running the UserForm

Your UserForm is now ready to use. Run the Macro called Run_UserForm. A UserForm with the caption Change Font Size will load.

Select the worksheet names from the list of worksheets on which you want to change the font sizes. Then choose the desired font size.

Here I’ve chosen Sheet1, Sheet2, and 20.

Then click OK. You’ll find the font sizes of Sheet1 and Sheet2 changed to 20.

Read More: How to Format Text to Capitalize First Letter in Excel (10 Ways)

Things to Remember

Here I’ve used the Worksheets object of VBA to access multiple worksheets of a workbook. If you want to access the worksheets of another workbook, you can use the Workbook object of VBA.

Conclusion

So, these are the ways to change the font size of the whole sheet with VBA in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our page ExcelDemy for more posts and updates.

Related Articles

• How to Capitalize First Letter of Each Word in Excel (4 Ways)
• Format Cell and Center Text with Excel VBA (5 Ways)
• How to Change Lowercase to Uppercase in Excel Without Formula
• Change Case in Excel without a Formula (5 Ways)
• How to Capitalize Each Word in Excel (7 Ways)
• How to Capitalize the First Letter in Excel (3 Methods)