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)
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.
__________________
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.
Does anyone knows how to create tables from data validation.
Thanks for your help and time 🙂
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
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
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!
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.
Kevin, instead of putting the code into a worksheet module, put it into the ThisWorkbook module, as a Workbook_SheetSelectionChange event
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