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?

 

[rickwithers]

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)

Yes, I knew there had to be easier ways to write code, but I am quite the novice and have been scouring the web in search of assistance whenever I had a issue I needed to address. You input is greatly appreciated. Chances are, I will be back here with some other "delemma" sooner or later.

Rick

 

[shg]

Actually,

    Worksheets(Range("H32").Text)).Select

 

[rickwithers]

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

Stepping through kept getting hung up on the Worksheets statement. However, using the mouse hover, I found the following:

sRng = ""
Range("BA6").Value = AH61 (which is correct.)

Range(sRng).Select sRng = ""

However, once I click the control, the MsgBox show "A1:AH61." So it appears the module is returning the correct info, just not zooming as needed.

 

[rickwithers]

Okay, I am still having this problem. Is it safe to assume the ActiveWindow.Zoom will not work from a called module? I have tried writing this code into individual worksheet codes, however, Each sheet already has code for Worksheet_SelectionChange. Placing the zoom in these sheets causes a looping issue, and the zoom still does not act as needed. For reference, here is the code for each worksheet that might be opened by the Sub Open_Ref() Module

<CODE>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim rng As Range
Dim sRange As Range
Dim fCell As Range
Dim tRange As String

ActiveSheet.Unprotect

Set rng = Range("A4:AE100")
rng.Interior.ColorIndex = 0

Set sRange = Range("BQ3:BQ100")
For Each fCell In sRange

If fCell.Value <> "" Then

tRange = fCell.Value

Range(tRange).Interior.ColorIndex = 8

End If

Next fCell

'ActiveSheet.Protect

End Sub

<END CODE>

 

[shg]

?? There's no code to zoom there ...

 

[rickwithers]

?? There's no code to zoom there ...

Yes, I realize that. The zoom should be called from a module which opens the applicable worksheet. Here is that code again:
Sub Open_Ref()
Dim sRng As String

Worksheets(Range("H32").Text).Select
sRng = "A1:" & Range("BA6").Text

Range(sRng).Select
ActiveWindow.Zoom = True
ActiveSheet.Protect
End Sub

This code opens an existing worksheet and, I was hoping, would set the zoom level of that sheet. It doesn't quite do that.

 

[shg]

I don't think I have another suggestion -- it works fine for me.

Are you sure it's getting called at the appropriate time?

 

[rickwithers]

I don't think I have another suggestion -- it works fine for me.

Are you sure it's getting called at the appropriate time?

I can't be sure, but it seems so. It gets called from a main page when a control button is clicked. Here is the code again with comments:

Sub Open_Ref()
Dim sRng As String

'Get the value from cell H32 of the main page and open the corresponding worksheet
Worksheets(Range("H32").Text).Select

'Get the value of cell BA6 from the opened worksheet and concatenate the needed range
sRng = "A1:" & Range("BA6").Text

'Select the range resulting from previous line
Range(sRng).Select

'Zoom to range selection (This is where the problem appears to occur; zoom level defaults to 400% on cell A1)
ActiveWindow.Zoom = True

ActiveSheet.Protect
End Sub


I assume all code should run prior to exiting the module. I can manually change the zoom of the target sheet, but running this module causes the zoom to default to 400%.

 

[shg]

If you comment out the Zoom line, it the correct range selected?

 

[rickwithers]

If you comment out the Zoom line, it the correct range selected?

YEs, if I use MsgBox sRng, it returns the correct range.