Found a workaround. The short answer is you can't.
But these 3 methods will work. I chose method #2.
------------------------------------------------------------------
http://www.contextures.com/xlDataVal08.html
------------------------------------------------------------------
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 a data validation list is selected
The following code will change the zoom setting to 120% when any cell with a data validation list is selected.
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
