Apr 06 2021 08:19 AM
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?
Apr 06 2021 08:43 AM
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
Mar 07 2022 06:44 PM
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