Setting zoom without select in Excel VBA

Question

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?

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.
Selection triggers events. Of course I can disable the events but disabling the events has some side effects which I want to avoid.

related http://stackoverflow.com/questions/9414426/excel-2007-vba-zooming-without-using-select — Raystafarian, Oct 12 '16 at 19:37

Comintern · Answer 1 · 2016-09-05T14:21:39.843

3

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

edited Sep 05 '16 at 14:21

answered Sep 05 '16 at 14:08

Comintern

21,855
5
33
80

It does not pass the test when some columns are hidden. Is it possible to get a true range width without unhiding hidden columns? – Przemyslaw Remin Sep 07 '16 at 11:23
1

@PrzemyslawRemin - You can, but you'll need to calculate it manually by looping over the columns. If they aren't hidden (i.e. `rng.Columns(1).Hidden`), add the width. – Comintern Sep 07 '16 at 12:11
Some columns in the range may be hidden, depending on user choice. I am looking for solution without unhidding any of them. – Przemyslaw Remin Sep 07 '16 at 12:33
1

@PrzemyslawRemin - You don't *have* to hide them. `If rng.Columns(x).Hidden = False Then width = width + rng.Columns(x).Width` – Comintern Sep 07 '16 at 12:37

score 2 · Answer 2 · answered Sep 05 '16 at 14:03

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

score 0 · Answer 3 · answered Jul 25 '21 at 19:27

0

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

Review this samples : Zoom the selected cells

answered Jul 25 '21 at 19:27

kadrleyn

364
1
5

score -1 · Accepted Answer · answered Oct 12 '16 at 20:00

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

Setting zoom without select in Excel VBA

4 Answers4