How to increase drop down list font size in Excel?

Drop down list is a very helpful feature in Excel, when you create drop down list in your worksheet, have you ever tried to increase the font size of the drop down list to make the selected record larger and more readable as left screenshot shown? This article will talk about a workaround to solve it.

Increase drop down list font size with VBA code to enlarge the zoom

Increase drop down list font size with VBA code to enlarge the zoom

In fact, there is no direct way for us to increase the font size of the drop down list, but, you can apply the following VBA code to change the drop down list cells zoom setting to make the text appear larger. Please do as follows:

1. Go the worksheet which contains the drop down list that you want to increase its font size.

2. Right click the sheet tab, and select View Code from the context menu, in the popped out Microsoft Visual Basic for Applications window, please copy and paste the following code into the blank Module window:

VBA code: Increase drop down list font size to enlarge the cell zoom

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'updateby Extendoffice
    On Error GoTo LZoom
    Dim xZoom As Long
    xZoom = 100
    If Target.Validation.Type = xlValidateList Then xZoom = 130
LZoom:
    ActiveWindow.Zoom = xZoom
End Sub

3. Then save and close this code, now, when you click the drop down list cells, the cell’s zoom will be enlarged, and the drop down list will be appeared larger, but, when you click other cells not contain the drop down list, the cell zoom will be normal.

Note: In the above code, xZoom = 130 will change the drop down list cells zoom to be 130, you can change this zoom number to your need.

Create drop down list with checkboxes in Excel

If you need to create drop down list with multiple checkboxes in order to select multiple items from the list into one cell, maybe there is no direct way for you to solve this task. But, Kutools for Excel support an amazing feature - Drop-down List with Check Boxes, with this feature, you can quickly create drop-down list with checkboxes in a specified range, current worksheet, current workbook or all opened workbooks based on your needs. Click to download Kutools for Excel!

Demo: Increase drop down list font size with VBA code to enlarge the zoom

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Read More... Free Download... Purchase...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Read More... Free Download... Purchase...

Comments (27)

No ratings yet. Be the first to rate!

Michael

about 6 years ago

#17535

This comment was minimized by the moderator on the site

So, this code makes the whole sheet zoom, not just the drop down box. It also says "cannot save...... VB Project in a macro disabled workbook". Blah blah blah. If I have to hunt all over hell's half acre to get a complete answer to this issue, this is not an answer.

Harlem Eins Michael

about 5 years ago

#17536

This comment was minimized by the moderator on the site

[quote]So, this code makes the whole sheet zoom, not just the drop down box. It also says "cannot save...... VB Project in a macro disabled workbook". Blah blah blah. If I have to hunt all over hell's half acre to get a complete answer to this issue, this is not an answer.By Michael[/quote] Simply save the workbook as xlsm and follow the steps again. BTW I tried it and it does NOT work for me at all; zoom is still at 100%.

Harlem Eins

about 5 years ago

#18519

This comment was minimized by the moderator on the site

I'm running Office 2016 and using drop-down boxes. I tried the "Increase drop down list font size with VBA code to enlarge the zoom" but it does not work. Is there anything else I should do to make it work? Thank you!

Travis

about 5 years ago

#20025

This comment was minimized by the moderator on the site

Like the directions indicate, you must change the zoom from "100" to "130" or a different zoom to see any changes. However, the 1st comment is accurate in that it does not just change the drop down menu, but rather once you click on the cell it will zoom the entire sheet 130% to zoom in on that cell. Not very helpful because then you just have to zoom back out every time.

garth Travis

about 4 years ago

#20026

This comment was minimized by the moderator on the site

or select an alternate cell in the workbook to return it to 100 as stated in the Macro

Private Sub Worksheet_SelectionChange(ByVal Target As Range)'updateby Extendoffice 20160530 On Error GoTo LZoom Dim xZoom As Long xZoom = 100 If Target.Validation.Type = xlValidateList Then xZoom = 130LZoom: ActiveWindow.Zoom = xZoomEnd Sub

Noel

about 5 years ago

#20653

This comment was minimized by the moderator on the site

Hi, Just wondering why this feature disappear after the file is saved and re-open?

Miemie Yang Noel

about 5 years ago

#20654

This comment was minimized by the moderator on the site

Hi, Noel, you should save the Excel file as Excel Macro-Enabled Workbook format.

Report

Seweryn Miemie Yang

about 5 months ago

#38244

This comment was minimized by the moderator on the site

Dzięki za podpowiedź

Ahmed Sobhy

about 5 years ago

#20950

This comment was minimized by the moderator on the site

Thanks, it works well

John Driscoll

about 4 years ago

#23339

This comment was minimized by the moderator on the site

This does not work for me. I pasted the text as instructed. When i clicked on the drop down menu the font size was the same. When i then clicked on a different cell, the whole spreadsheet increased in size perhaps by 30%. Really would like a work around if possible

George

about 4 years ago

#23372

This comment was minimized by the moderator on the site

After using this code, I can't use return key on Excel. How can I code (install) return key?

Dave Harlev

about 4 years ago

#24171

This comment was minimized by the moderator on the site

This zooms the whole sheet to 130%
How do I increase only the drop menu ?

Report

skyyang Dave Harlev

about 4 years ago

#24172

This comment was minimized by the moderator on the site

Hello, Dave,
To only increase the drop down menu, here is a workaround to solve it.

First, after inserting the drop down list in your worksheet, then you should insert an Combo Box (ActiveX Controls),and then apply the following VBA code into the sheet module code:

Dim xRg As Range
Private Sub ComboBox1_Change()
On Error Resume Next
xRg.Value = Me.ComboBox1.Text
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo LblExit
With Me.ComboBox1
.Visible = False
If Target.Validation.Type = xlValidateList Then
Target.Validation.InCellDropdown = False
.ListWidth = 120
.ListFillRange = ""
.ListFillRange = Target.Validation.Formula1
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width
.Height = Target.Height
.Font.Size = 16
.Visible = True
Set xRg = Target
End If
End With
LblExit:
End Sub

After inserting the code, now when you click the drop down list cell, a combo box will be displayed, and font size in the drop down menu is increased as you need. And the inserted combo box will be hidden automatically.

Please try it, hope it can help you!

Report

GJ skyyang

about 3 years ago

#24173

This comment was minimized by the moderator on the site

GENIUS! Thank you Skyyang! So grateful for your method! Works like a charm!

ITALO BELMONTE skyyang

about 3 years ago

#24174

This comment was minimized by the moderator on the site

Can you explain it step by step for dummies pls?

skyyang ITALO BELMONTE

about 3 years ago

#24175

This comment was minimized by the moderator on the site

Hello, ITALO,
1. Insert the drop down you need in your worksheet;
2. Then insert a Combo Box (ActiveX Controls) by clicking Developer > Insert > Combo Box (ActiveX Controls), and then drag the mouse to draw a Combo Box object;
3. And then, right click your current used worksheet, choose View Code, and paste the above code into the module;
4. At last, click Design Mode under the Developer tab to disable it.
5. Now when you click the drop down list cell, a combo box will be displayed, and font size in the drop down menu is increased as you need.
Please try, hope it can help you!

Report

Paul Roberts

about 4 years ago

#25045

This comment was minimized by the moderator on the site

How can I apply this font change size to only one column? I have several columns with drop-down boxes and I only want one specific column to change font size?

Report

Ace

about 3 years ago

#27449

This comment was minimized by the moderator on the site

Hi, Just question i have pasted the code onto blank Module window (Right click in view code). Once i save, is function well that was good. But when i close the file and re-open it, the code was gone and no more enlarge for the drop down list. Can i know how to set for permanent use?

skyyang Ace

about 3 years ago

#27450

This comment was minimized by the moderator on the site

Hello, Ace,
To save the code, you should save the Excel file as Excel Macro-Enabled Workbook format. Please try it.

Report

Yvonne skyyang

about 1 month ago

#40658

This comment was minimized by the moderator on the site

I have multiple dropdowns and only need one bigger as the rest are yes/no. But the code you shared is doing this for all of them. How do I only this to be used for one dropdown only?

skyyang Yvonne

about 1 month ago

#40707

This comment was minimized by the moderator on the site

Hello, Yvonne
To apply the code only to one cell, the following code may help you: (Note: in the code, change the cell I2 to your own cell reference.)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'updateby Extendoffice
    On Error GoTo LZoom
    Dim xZoom As Long
    Dim xRg As Range
    xZoom = 100
    Set xRg = Range("I2")
    If Not Intersect(Target, xRg) Is Nothing Then
        If Target.Validation.Type = xlValidateList Then xZoom = 130
    End If
LZoom:
    ActiveWindow.Zoom = xZoom
End Sub

Please try, hope it can help you!

Report

David Frost

about 2 years ago

#33311

This comment was minimized by the moderator on the site

Thank You for the tip to zoom the size of the font in the drop down box. It did the trick.

Iván

about 8 months ago

#37682

This comment was minimized by the moderator on the site

Ocurre al revés. Al dar clic en cualquiera celda se agranda, pero no en la lista desplegable y no vuelve al tamaño original. Uso Excel 2010. ¿Solución?It happens the other way around. Clicking on any cell enlarges it, but not the dropdown list and it does not return to the original size. I use Excel 2010. Solution?