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...

19 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
David Frost · 1 years ago
Thank You for the tip to zoom the size of the font in the drop down box. It did the trick.
- Reply
To post as a guest, your comment is unpublished.
Ace · 2 years ago
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?
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 2 years ago
  
  Hello, Ace,
  To save the code, you should save the Excel file as Excel Macro-Enabled Workbook format. Please try it.
  
  Reply
To post as a guest, your comment is unpublished.
Paul612 · 3 years ago
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?
- Reply
To post as a guest, your comment is unpublished.
david.harlev@gmail.com · 3 years ago
This zooms the whole sheet to 130%
How do I increase only the drop menu ?
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 3 years ago
  
  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!
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  ITALO BELMONTE · 2 years ago
  
  Can you explain it step by step for dummies pls?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  skyyang · 2 years ago
  
  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!
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  GJ · 2 years ago
  
  GENIUS! Thank you Skyyang! So grateful for your method! Works like a charm!
  
  Reply
To post as a guest, your comment is unpublished.
George · 3 years ago
After using this code, I can't use return key on Excel. How can I code (install) return key?
- Reply
To post as a guest, your comment is unpublished.
John Driscoll · 3 years ago
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
- Reply
To post as a guest, your comment is unpublished.
Ahmed Sobhy · 3 years ago
Thanks, it works well
- Reply
To post as a guest, your comment is unpublished.
Noel · 3 years ago
Hi, Just wondering why this feature disappear after the file is saved and re-open?
- Reply
- To post as a guest, your comment is unpublished.
  miemieyang005@gmail.com · 3 years ago
  
  Hi, Noel, you should save the Excel file as Excel Macro-Enabled Workbook format.
  
  Reply
To post as a guest, your comment is unpublished.
Travis · 4 years ago
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.
- Reply
- To post as a guest, your comment is unpublished.
  garth · 3 years ago
  
  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
  
  Reply
To post as a guest, your comment is unpublished.
Harlem Eins · 4 years ago
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!
- Reply
To post as a guest, your comment is unpublished.
Michael · 4 years ago
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.
- Reply
- To post as a guest, your comment is unpublished.
  Harlem Eins · 4 years ago
  
  [quote name="Michael"]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.[/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%.
  
  Reply

Login to your account

Password Reset