determining the zoom of the current sheet.

  • does anyone know how i can determine the zoom number of a sheet .

    as what i need to do is chage the zoom of the sheet then put it back to the zoom number it was originally.

    the original zoom could be anything that the user has determined by zooming the size to the required size. therefore could be allmost anything .

    any help would be greatfully recieved.

  • thanks for reply andy.

    but what i need to determine is the actually zoom number when i initially get onto the sheet.

    say the sheet is set at zoom of 67 then i need i know what that number is .

    so i need to look at the sheet in some way and determine that 67 is the number the sheet has been zoomed to.

    then i can change the zoom. and the after i have finshed with what i am doing then i can put it back to 67.

    hope i have explained myself clearly.

    but thankyou andy for the reply.

  • thanks andy , but i still cant seem to do what i need .

    let me explain again.

    i need to get the current value of the zooom on the sheet then store that somewhere.

    then i need to zoom my sheet to a preset zoom figure from a figure i have chosen.

    then on sheet calculate the zoom will go back to the original zoom number which i stored earleier.

    all i need reall is the actual figure in a cell on a sheet or somewhere so i can store that number to come back to later.

    maybe i have explained myself better this time.

    or maybe what you have said can do this but i cant seem to make it do this .

    hope you can help.

    thanks for your time.

  • hi Nunny,

    place this code in the ThisWorkbook area in VBE

    Private Sub Workbook_Open()
    Sheets("sheet1").Range("A1").Value = ActiveWindow.Zoom
    End Sub


    Xlite :sheep:
    [st]All you need to learn VBA is an internet connection and[/st]

  • you might also wanna add this code (also in ThisWorkbook section),
    its to revert back to its zoom size when you first open the book.
    the code looks at the value in A1 to zoom it when you Save the workbook

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ActiveWindow.Zoom = Sheets("sheet1").Range("A1").Value
    End Sub


    Xlite :sheep:
    [st]All you need to learn VBA is an internet connection and[/st]

  • Try the following : Start recording a macro and do the following :
    select a range across the sheet that you would like to be the width of your screen and choose zoom to selected. Then end the macro and edit it and if you run this when the sheet becomes active then no matter what the zoom had been changed to, it will always return to what you want it at.
    If you still have problems then I can sent you the code from South Africa.

  • Hi Nunny,

    you're overwelmed by advise, so let me also give some to you.
    It works for me.

    Type this in your 'ThisWorkbook' Or better... copy it.



  • Thanks for all the help everyone , i have managed to do what i needed with a combination of things from the replys .

    thank you all for your help.


  • :facepull: Yep, proves there is often more than one way to get the job done.

    Barbara - aka The Cat Lady :cat:

    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

Participate now!

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