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