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

Question

I find the small font size when using data validation annoyingly small as I am usually on 70% zoom.&nbsp;I have found the following vba code which automatically zooms in when a dropdown list is selected:&nbsp;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&nbsp;I would like help to write some code that automatically changes the zoom to 70% once a drop down value has changed if possible?&nbsp;&nbsp;

hansvogelaar · Answer

DBreezy92&nbsp;
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 · Answer

Another way is to store the original .zoom value&nbsp;Private Sub Worksheet_SelectionChange(ByVal Target As Range)&nbsp; &nbsp; &nbsp; &nbsp;Application.EnableEvents = True&nbsp; &nbsp; &nbsp; &nbsp;Dim dv As XlDVType&nbsp; &nbsp; &nbsp; &nbsp;Dim Oldzoom As Integer&nbsp; &nbsp; &nbsp; &nbsp;On Error Resume Next&nbsp; &nbsp; &nbsp; dv = ActiveCell.Validation.Type&nbsp; &nbsp; &nbsp; On Error GoTo 0&nbsp; &nbsp; &nbsp; If dv = xlValidateList Then&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Oldzoom = ActiveWindow.Zoom&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Sheets("Data").Range("Z1") = Oldzoom&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ActiveWindow.Zoom = 100&nbsp; &nbsp; &nbsp; Else&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Oldzoom = Sheets("Data").Range("Z1")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ActiveWindow.Zoom = Oldzoom&nbsp; &nbsp; &nbsp;End IfEnd Sub

Forum Discussion

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

2 Replies

Resources