ZoomtoRange

[reece1984]

Hi,

Could somebody please tell me how to zoom to a range in vba. The code that I have at the moment seems to be incorrect.




Sub zoom_to_complete_spend_rate()
'
' zoom_to_complete_spend_rate Macro
'
ActiveWindow.ZoomToRange = "to_complete_spend_rate"

End Sub




Thank you,

J

 

[AlphaFrog]

This uses the ZoomToRange function from cpearson.com

It assumes you have a named range called "to_complete_spend_rate"

[color=darkblue]Sub[/color] zoom_to_complete_spend_rate()
    ZoomToRange Range("to_complete_spend_rate"), [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

[color=darkblue]Sub[/color] ZoomToRange([color=darkblue]ByVal[/color] ZoomThisRange [color=darkblue]As[/color] Range, _
                [color=darkblue]ByVal[/color] PreserveRows [color=darkblue]As[/color] [color=darkblue]Boolean[/color])
                
    [color=green]'http://www.cpearson.com/excel/zoom.htm[/color]

    [color=darkblue]Dim[/color] Wind   [color=darkblue]As[/color] Window

    [color=darkblue]Set[/color] Wind = ActiveWindow
    Application.ScreenUpdating = [color=darkblue]False[/color]
    [color=green]'[/color]
    [color=green]' Put the upper left cell of the range in the top-left of the screen.[/color]
    [color=green]'[/color]
    Application.Goto ZoomThisRange(1, 1), [color=darkblue]True[/color]

    [color=darkblue]With[/color] ZoomThisRange
        [color=darkblue]If[/color] PreserveRows = [color=darkblue]True[/color] [color=darkblue]Then[/color]
            .Resize(.Rows.Count, 1).Select
        [color=darkblue]Else[/color]
            .Resize(1, .Columns.Count).Select
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]

    [color=darkblue]With[/color] Wind
        .Zoom = [color=darkblue]True[/color]
        .VisibleRange(1, 1).Select
    [color=darkblue]End[/color] [color=darkblue]With[/color]

[color=darkblue]End[/color] Sub

 

[reece1984]

Thanks Alpha Frog, that works fine although I was expecting it to be simpler so that I could use it in the following.

I have a worksheet with 20 small charts that I wanted to develop so that if a chart is selected it is displayed large on the screen, and if it is selected again, the zoom settings are returned to zoomed out to the full page.

I was hoping to apply it to an IF function such as:

If ZoomToRange_1 Then
ZoomToRange_full_page
Else
ZoomToRange_1
End

I thought that it would be simple to apply this IF function provided I knew how to zoom to range.

Can anybody help with this?

Thank you

J

 

[AlphaFrog]

I thought that it would be simple to apply this IF function provided I knew how to zoom to range.

• Start recording a macro
• Select a range (any range) on the worksheet.
• Select from the menu; View\ Zoom: Fit Selection
• Stop recording.

This will give you example code that "zooms to a range". You could use this recorded code syntax to incorporate it in your final macro. You would see it is a simplistic version of what the ZoomToRange function (previous code) does.


Below will toggle the zoom between the currently selected range fit to screen and 100%

    If ActiveWindow.Zoom = 100 Then
        ActiveWindow.Zoom = True
    Else
        ActiveWindow.Zoom = 100
    End If