CODEWITHSUNDEEP
excelvbazoomingactive-window
Przemyslaw Remin
Przemyslaw Remin

Reputation: 6950

Setting zoom without select in Excel VBA

Is it possible to set up zoom level based on screen resolution without Select?

I have followed the code:

Sheets(1).Range("A1:AC1").Select
ActiveWindow.Zoom = True

taken from https://stackoverflow.com/a/19439177/1903793

Desired code would be something like:

Range("A1:AC1").Width.Zoom=True

Update. Why do I want to avoid Select?

  1. My sheet has hidden columns based on user settings. So some columns of the range A1:AC1 are hidden. I cannot select single column because this particular column may be hidden.
  2. Selection triggers events. Of course I can disable the events but disabling the events has some side effects which I want to avoid.

Upvotes: 4

Views: 4905

Answers (4)

kadrleyn
kadrleyn

Reputation: 394

Zooming can be done easily by creating a larger image of the selected cells (like 1.5 times) or by increasing the font size :

enter image description here

enter image description here

Review this samples : Zoom the selected cells

Upvotes: 0

Przemyslaw Remin
Przemyslaw Remin

Reputation: 6950

That is what I have ended up with. Solution is resistant to hidden columns. I do not select columns but shape. Add a rectangle, name it "BoxForZoom". It should be wide just enough for your zoom. Then apply the following code:

Sheet1.Shapes("BoxForZoom").Visible = True
Sheet1.Shapes("BoxForZoom").Select
ActiveWindow.Zoom = True
Sheet1.Shapes("BoxForZoom").Visible = False

Upvotes: -1

Comintern
Comintern

Reputation: 22205

Just measure the current window width and the range's width. Then you can use those values to set a scaling ratio. Note - this needs some additional validation and error handling, but it should give the basic idea.

Private Sub ZoomToRange(target As Range)
    'Get the window from the target range.
    Dim wnd As Window
    Set wnd = ActiveWindow

    'Find out what you need to scale to.
    Dim scaling As Long
    scaling = 100 * wnd.Width / target.Width
    'Limit to max and min zoom level.

    If scaling > 400 Then
        wnd.Zoom = 400
    ElseIf scaling < 10 Then
        wnd.Zoom = 10
    Else
        wnd.Zoom = scaling
    End If
    'Scroll to the upper left cell
    target.Cells(1, 1).Activate
End Sub

Upvotes: 3

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96791

This works, but will not make you happy.

Since we want column AC (which is column 29) to be visible, we start with Zoom = 100 and reduce it one step at a time until there are 29+1 columns in the VisibleRange:

Sub ShrinkWindow()
    Dim i As Long, r As Range

    For i = 100 To 1 Step -1
        ActiveWindow.Zoom = i
        Set r = ActiveWindow.VisibleRange
        If r.Columns.Count = 29 + 1 Then Exit Sub
    Next i
End Sub

Upvotes: 2

Related Questions