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

Copper Contributor

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 selected:

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim KeyCells As Range
Set KeyCells = Range("DropDown")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

ActiveWindow.Zoom = 100

End If

End Sub

 

I would like help to write some code that automatically changes the zoom to 70% once a drop down value has changed if possible?

 

 

2 Replies

@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

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