Forum Discussion

Copper Contributor

Apr 06, 2021

VBA code to zoom out when an option is selected on a data validation list

I find the small font size when using data validation annoyingly small as I am usually on 70% zoom. I have found the following vba code which automatically zooms in when a dropdown list is select...

Macros and VBA

HansVogelaar

MVP

Apr 06, 2021

DBreezy92

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim dv As XlDVType
    On Error Resume Next
    dv = ActiveCell.Validation.Type
    On Error GoTo 0
    If dv = xlValidateList Then
        ActiveWindow.Zoom = 100
    Else
        ActiveWindow.Zoom = 70
    End If
End Sub

Dean_Australia

Copper Contributor

Mar 08, 2022

Another way is to store the original .zoom value

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = True
Dim dv As XlDVType
Dim Oldzoom As Integer
On Error Resume Next
dv = ActiveCell.Validation.Type
On Error GoTo 0
If dv = xlValidateList Then
Oldzoom = ActiveWindow.Zoom
Sheets("Data").Range("Z1") = Oldzoom
ActiveWindow.Zoom = 100
Else
Oldzoom = Sheets("Data").Range("Z1")
ActiveWindow.Zoom = Oldzoom
End If
End Sub