VBA Zoom to Selection

[rickwithers]

I have the following code to try to zoom to a selection dependent on the worksheet being opened:

zArea = "A1:" & Range("BA6").value (Sets String zArea to A1: and the last cell in range as given in cell BA6 on each worksheet)

Range(zArea).Select

ActiveWindow.Zoom = True

The problem I have is the correct sheet opens, but the zoom level defaults to 400% at cell A1. For one particular worksheet, cell BA6 = AH61, therefore zArea = A1:AH61. Using MsgBox confirmed the value, however, the Zoom property doesn't accept it. Any suggestions?

 

[Andrew Poulsom]

Welcome to MrExcel.

This worked for me:

Sub MyZoom()
    Dim zArea As String
    zArea = "A1:" & Range("BA6").Value
    Range(zArea).Select
    ActiveWindow.Zoom = True
End Sub

Doesn't it work for you?

 

[Rick Rothstein]

I have the following code to try to zoom to a selection dependent on the worksheet being opened:

zArea = "A1:" & Range("BA6").value (Sets String zArea to A1: and the last cell in range as given in cell BA6 on each worksheet)

Range(zArea).Select

ActiveWindow.Zoom = True

The problem I have is the correct sheet opens, but the zoom level defaults to 400% at cell A1. For one particular worksheet, cell BA6 = AH61, therefore zArea = A1:AH61. Using MsgBox confirmed the value, however, the Zoom property doesn't accept it. Any suggestions?

What do you mean by "the correct sheet opens"? Are you running your code from a different sheet than the one you are zooming in on? If so, I think it might be helpful if you show us all your code... I am interested mainly in when you activate the other sheet because it sounds like some of what you are doing is defaulting to the sheet you are running the code from and not the sheet you are attempting to zoom in on.

 

[rickwithers]

OK here is the code from the module I run:

Sub Open_Ref()
Dim MyRef As String
Dim Ref As Long
Dim zArea As String

' Get the sheet number to open Looks for value in cell H32 of main sheet
Ref = Range("H32").Value

If Ref = 8 Then
MyRef = "8"

ElseIf Ref = 9 Then
MyRef = "9"

ElseIf Ref = 10 Then
MyRef = "10"

ElseIf Ref = 11 Then
MyRef = "11"

ElseIf Ref = 12 Then
MyRef = "12"

ElseIf Ref = 13 Then
MyRef = "13"

ElseIf Ref = 14 Then
MyRef = "14"
ElseIf Ref = 15 Then
MyRef = "15"

ElseIf Ref = 16 Then
MyRef = "16"

ElseIf Ref = 17 Then
MyRef = "17"

ElseIf Ref = 18 Then
MyRef = "18"

ElseIf Ref = 19 Then
MyRef = "19"

ElseIf Ref = 20 Then
MyRef = "20"

ElseIf Ref = 21 Then
MyRef = "21"

ElseIf Ref = 22 Then
MyRef = "22"

ElseIf Ref = 23 Then
MyRef = "23"

ElseIf Ref = 24 Then
MyRef = "24"

ElseIf Ref = 25 Then
MyRef = "25"

ElseIf Ref = 26 Then
MyRef = "26"
ElseIf Ref = 27 Then
MyRef = "27"
ElseIf Ref = 28 Then
MyRef = "28"
ElseIf Ref = 29 Then
MyRef = "29"
ElseIf Ref = 30 Then
MyRef = "30"
ElseIf Ref = 31 Then
MyRef = "31"
ElseIf Ref = 32 Then
MyRef = "32"
ElseIf Ref = 33 Then
MyRef = "33"
ElseIf Ref = 34 Then
MyRef = "34"
ElseIf Ref = 35 Then
MyRef = "35"
ElseIf Ref = 36 Then
MyRef = "36"
ElseIf Ref = 37 Then
MyRef = "37"
ElseIf Ref = 38 Then
MyRef = "38"
ElseIf Ref = 39 Then
MyRef = "39"
ElseIf Ref = 40 Then
MyRef = "40"
ElseIf Ref = 41 Then
MyRef = "41"
ElseIf Ref = 42 Then
MyRef = "42"
ElseIf Ref = 43 Then
MyRef = "43"
ElseIf Ref = 44 Then
MyRef = "44"
ElseIf Ref = 45 Then
MyRef = "45"
ElseIf Ref = 46 Then
MyRef = "46"
ElseIf Ref = 47 Then
MyRef = "47"
ElseIf Ref = 48 Then
MyRef = "48"
ElseIf Ref = 49 Then
MyRef = "49"
ElseIf Ref = 50 Then
MyRef = "50"

End If

Worksheets(MyRef).Select 'opens the work I need open

' Find the range to zoom to
zArea = "A1:" & Range("BA6").Value

'MsgBox zArea

Range(zArea).Select

ActiveWindow.Zoom = True

ActiveSheet.Protect

End Sub

 

[Andrew Poulsom]

And where is that code? In a General module or in a Worksheet module?

 

[rickwithers]

General Module called when a control button is clicked. It causes the correct worksheet to open, but the zoom is incorrect.

 

[shg]

Try this:

Sub Open_Ref()
    Dim sRng As String
    

    sRng = "A1:" & Range("BA6").Value
    Worksheets(CStr(Range("H32").Value)).Select
    Range(sRng).Select
    ActiveWindow.Zoom = True
    ActiveSheet.Protect
End Sub

 

[Rick Rothstein]

General Module called when a control button is clicked. It causes the correct worksheet to open, but the zoom is incorrect.

Not sure what to tell you... your code works fine for me. Maybe Andrew will have some ideas for you when he comes back to the thread.

I will tell you for future reference, though, that your entire long If..Then...ElseIf..etc... End If block of code can be replaced with this single line of code...

MyRef = CStr(Ref)

 

[rickwithers]

OK, gave it a shot. The above code tries to get the value of BA6 from the main sheet, which is empty, before making the object sheet active. The problem using this method is each worksheet needs a different zoom selection value, so I can't put a generic value in the main sheet.

I tried moving the sRng below the Worksheets function with the same results I initially posted. I do like the simplicity of the Worksheets function you posted, a lot less code to deal with.

It looks like it's going to be difficult to get the zoom working properly from a general module.

 

[shg]

If the range is on the destination sheet, then you made the right change. Step through the code and see what's going on.