GOTO failed

[rjplante]

I have a workbook that has different size spreadsheets and I would like to have some code that would fill the screen based on a set range. The number of columns is what I am concerned with not the number of rows, as the user will be able to use the scroll wheel to see the lower rows. If the users screen is large enough and their resolution set high enough to display the who defined range, I would like it to zoom to fit the defined columns.

I have implemented the code below, but when I use this with some of the pages in my workbook, I get the following error: "Run-time error '1004': Method 'Goto' of object '_Application' failed". The line in red is what is highlighted.

I don't know why it works for some and then crashes on others. What is wrong with this line of code?

Thanks,

Robert

=================================

This block of code appears in a global use module.

Sub Zoom_to_Range(ByVal ZoomThisRange As Range, _
ByVal PreserveRows As Boolean)

Dim wind As Window

Set wind = ActiveWindow

Application.ScreenUpdating = False

Application.Goto ZoomThisRange(1, 1), True

With ZoomThisRange
    If PreserveRows = True Then
        .Resize(.Rows.Count, 1).Select
    Else
        .Resize(1, .Columns.Count).Select
    End If
End With

With wind
    .Zoom = True
    .VisibleRange(1, 1).Select
End With

Application.ScreenUpdating = True

End Sub

This block of code appears in the individual worksheet code window.

Private Sub Worksheet_Activate()

'zooms page to fit on screen
Zoom_to_Range ZoomThisRange:=Range("A1:S65"), PreserveRows:=False

End Sub

 

[gaj104]

Hi,

Unsure why you have the problem, but your code could be very much simplified to:

Private Sub Worksheet_Activate()

'zooms page to fit on screen
Range("A1:S1").Select
ActiveWindow.Zoom = True
Range("A1").Select

End Sub

By selecting the range in only the first row the sheet will zoom to fit the columns and not the rows.

Hope it helps