Make Data Validation List Appear Larger

Make Data Validation List Appear Larger

When you create a drop down list with data validation, you can’t change the font or font size. If you have reduced the zoom setting for a worksheet, it can be difficult to read the items in the list. And even at 100%, it can tough to read the tiny print, at the end of a long workday. Here’s how you can make data validation list appear larger.

Zoom In to Read the List

To make the data validation text appear larger, you can use a bit of VBA code to increase the zoom setting when a data validation cell is selected. (Note: this can be a bit jumpy)

datavalidationzoom01

The following code will change the zoom setting to 120% when any cell with a data validation list is selected. If you select a cell without a data validation list, the zoom reduces to 100%.

datavalidationzoom02

The Zoom Code

Add this code to the worksheet module for the sheet with data validation cells.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim lZoom As Long
  Dim lZoomDV As Long
  Dim lDVType As Long
  lZoom = 100
  lZoomDV = 120
  lDVType = 0
  Application.EnableEvents = False
  On Error Resume Next
  lDVType = Target.Validation.Type
    On Error GoTo errHandler
    If lDVType <> 3 Then
      With ActiveWindow
        If .Zoom <> lZoom Then
          .Zoom = lZoom
        End If
      End With
    Else
      With ActiveWindow
        If .Zoom <> lZoomDV Then
          .Zoom = lZoomDV
        End If
      End With
    End If
exitHandler:
  Application.EnableEvents = True
  Exit Sub
errHandler:
  GoTo exitHandler
End Sub

More Zoom Options

To see other sample code for changing the zoom setting, see the Data Validation Tips page on the Contextures website.

__________________

10 thoughts on “Make Data Validation List Appear Larger”

  1. Debra – Nice little function.
    A couple thoughts for enhancement:
    1. Rather than setting the zoom back to a default value of 100%, it might be better to capture the current zoom value that the user is working with as the default
    2. How about setting zoom back to the default value after the new list value is selected (change event), rather than waiting for the next select event??
    APJ

    1. Thanks Alex, those are good ideas. You could set a variable at the top of the worksheet module, and use it to store the original zoom setting, any time a data validation list cell is selected.
      To prevent it from getting stuck on a larger zoom (if the data validation cell is still selected when the workbook is closed), you could go back to that setting when closing the workbook.

  2. Just noticed this code doesn’t seem to fire on validation that is set on merged cells.
    FYI – found on a sheet with validiation on both merged and unmerged cells – is called fine on non-merged cells with valiataion, but not merged cells

  3. I doubt this thread is still active, but is there a way to restore the zoom after the selection is made from the drop down list? My workbook has many columns with drop down tables and I have to hunt for a column without a data table so the zoom snaps back to default.

    1. Michael, you could add another worksheet procedure to change the zoom setting back to 100:
      Private Sub Worksheet_Change(ByVal Target As Range)
      ActiveWindow.Zoom = 100
      End Sub

  4. Just used this feature and added the March 2nd 2017 back to my desired zoom percent “85%.” It works and simple to follow. Thank you Debra from back in 2012!
    A little odd with clicking in cell and it changing zoom, but it is what I need. Working on a larger screen at 85% zoom, the drop down box is impossible to read with better than 20/20. Thus I have bad posture looking at my screen. Anyway, glad I found a better way to view my new drop down!

  5. Hi Debra

    Thank you for the code to increase the size of the drop down list, this is a great work around. What would one have to alter in the code to make the code work across the entire workbook and not just one single sheet.

  6. I saw somewhere someone suggested to reduce the font size of the entire sheet to 6 or 8, then zoom in. The smaller drop-down is now regular size

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.