CODEWITHSUNDEEP
excelvba
Ckeane
Ckeane

Reputation: 167

Excel VBA Code to Force a certain zoom level

I have a Validated Dropdown list in Excel that is unreadable if zoom is less than 100. I checked on the internet and fvound that I can not alter the size of the Validated list text size so I want to enforce a set zoom of 100.

I have the code to do this as follows

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ActiveWindow.Zoom = 100
End Sub

This works and is fine for people who use zoom less than 100, but if people use a greater than 100 zoom it restricts the zoom to 100. Is there a way to overcome this, something along the lines of an If-Else statement.

If zoom less than 100 then zoom = 100 else if zoom greater than 100 do nothing

Thanks.

Upvotes: 7

Views: 39484

Answers (3)

Noam Brand
Noam Brand

Reputation: 346

To address returning to the default previous zoom level (and prevent unnecessary zoom flickering I suggest:

'Assumed default zoom level= 70
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Temp As Byte
Temp = 0
        If Not Intersect(Target, Range("G3:H9999")) Is Nothing Then
             If ActiveWindow.Zoom <= 70 Then ActiveWindow.Zoom = 100
             Temp = 1
        End If
        
        If Not Intersect(Target, Range("E2:E9999")) Is Nothing Then
                If ActiveWindow.Zoom <=70 Then ActiveWindow.Zoom = 100
               Temp = 1
        End If
        
       If Temp = 0 And ActiveWindow.Zoom = 100 Then ActiveWindow.Zoom = 70
End Sub

Upvotes: 0

JimmyPena
JimmyPena

Reputation: 8754

Here's a one-liner that will do the same thing:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  ActiveWindow.Zoom = Application.Max(ActiveWindow.Zoom, 100)
End Sub

Upvotes: 6

Steven
Steven

Reputation: 781

If (ActiveWindow.Zoom < 100) Then

    ActiveWindow.Zoom = 100

End If

Upvotes: 13

Related Questions