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.

datavalidationzoom01 

Zoom In to Read the List

To make the 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)

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.

__________________

You may also like...

6 Responses

  1. AlexJ says:

    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

    • 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. Shelly Noble says:

    Does anyone knows how to create tables from data validation.
    Thanks for your help and time :)

  3. Duncan says:

    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

  4. Michael Vollmer says:

    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.

    • 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

Leave a Reply to Shelly Noble Cancel reply

Your email address will not be published. Required fields are marked *