zoom widow to a selected range

    VBA newbie here (but learning quickly)


    I am trying to zoom to a set of columns. (say G:M)


    I am aware of:
    [vba]
    Columns ("G:M").Select
    ActiveWindow.Zoom = True
    [/vba]
    But I am trying to do it without actually using the 'select' command. Let me explain:


    I have a worksheet that has the Worksheet_SelectionChange function and I am trying to have most of my code execute without having to disable Application.EnableEvents every time I want to do something. I am able to change cell vaues and properties without actually 'selecting' the cells i.e. :
    [vba]
    Range ("H9").Value = "hello"
    [/vba]
    or
    [vba]
    Range("H9").Interior.ColorIndex = 8
    [/vba]
    If I use the Select command then one of my functions forces the acive cell to column "A" and overrides the zoom area that I want. If Anyone can tell me how to zoom to a set of columns without having to disable events first it would be greatly appreciated.


    Regards,


    Jim

    Re: zoom widow to a selected range


    Hi,


    My first idea would be to build a 'Custom View'. Select what you want to see, 'View', and 'Fit Selection'. Then 'View', Custom Views', 'Add'. In your Sub just refer to the view name:


    [vba]
    ActiveWorkbook.CustomViews("al").Show
    [/vba]


    To restore to normal:
    [vba]
    Activewindow.zoom = 100
    [/vba]


    Cheers,


    dr

    Re: zoom widow to a selected range


    :thanx:
    Jack,


    Thanks for adding vba code tags. Still learning forum guidlines.


    DR:


    Thanks for the code suggestion. Although the custom view seems start with the correct selected column it seems to just remember the zoom level at time the view was saved. So if the macro is run when the app window is a different size or if the monitor resolution is different then the result is not correct. (however it does do it while events are enabled) :)


    I also want this macro to run on application resize. I understand the application resize procedure so what I am trying to achieve is to resize to Columns G to M so that is what the user sees under all situations.


    I guess I can disable events, run my macro and re enable events. I'm just on a mission to do this with as few lines of code as possible. In the past few days I have managed to cut most of my macros in half. It's kind of my way to gain knowledge of vba. Try, Try and Try again!


    So although I can use my current project, I'd still like to find an answer and if I do figure it out I'll certainly reply here.


    Cheers,


    Jim

    Re: zoom widow to a selected range


    Hi Jim


    I would say your answer is with the ScrollIntoView Method. E.g.

    Code
    Application.ActiveWindow.ActivePane.ScrollIntoView _
    Left:=Range("I1").Left, Top:=Range("I1").Top, _
    Width:=100, Height:=200, Start:=True

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login