SOURCE: http://www.contextures.com/xlDataVal08.html#Font
Data Validation Font Size and List Length
The font size in a data validation list can't be changed, nor can its default list length, which has a maximum of eight rows.
If you reduce the zoom setting on a worksheet, it can be almost impossible to read the items in the dropdown list, as in the example at right.
One workaround is to use programming, and a combo box from the Control Toolbox, to overlay the cell with data validation. If the user double-clicks on a data validation cell, the combobox appears, and they can choose from it. There are instructions here.
Make the Dropdown List Appear Larger
In a Data Validation dropdown list, you can't change the font or font size.
To make the text appear larger, you can use an event procedure (three examples are shown below) to increase the zoom setting when the cell is selected. (Note: this can be a bit jumpy)
Or, you can use code to display a combobox, as described in the previous section.
Zoom in when specific cell is selected
If cell A2 has a data validation list, the following code will change the zoom setting to 120% when that cell is selected.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$2" Then
ActiveWindow.Zoom = 120
Else
ActiveWindow.Zoom = 100
End If
End Sub
To add this code to the worksheet:
Right-click on the sheet tab, and choose View Code.
Copy the code, and paste it onto the code module.
Change the cell reference from $A$2 to match your worksheet.
Zoom in when specific cells are selected
If several cells have a data validation list, the following code will change the zoom setting to 120% when any of those cells are selected. In this example, cells A1, B3 and D9 have data validation.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1,B3,D9")) Is Nothing Then
ActiveWindow.Zoom = 100
Else
ActiveWindow.Zoom = 120
End If
End Sub
Zoom in when any cell with data validation is selected
The following code will change the zoom setting to 120% when any cell with data validation is selected.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngDV As Range
Dim intZoom As Integer
Dim intZoomDV As Integer
intZoom = 100
intZoomDV = 120
Application.EnableEvents = False
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo errHandler
If rngDV Is Nothing Then GoTo errHandler
If Intersect(Target, rngDV) Is Nothing Then
With ActiveWindow
If .Zoom <> intZoom Then
.Zoom = intZoom
End If
End With
Else
With ActiveWindow
If .Zoom <> intZoomDV Then
.Zoom = intZoomDV
End If
End With
End If
exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
GoTo exitHandler
End Sub
