determining the zoom of the current sheet.

Nunny · Sep 16th 2004

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.

**Andy Pope** · Sep 16th 2004

Hi Nunny,

Try using Activewindow.Zoom

Nunny · Sep 16th 2004

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.

**Andy Pope** · Sep 16th 2004

The property contains that number.

Sub X
Msgbox Activewindow.zoom
End sub

Stick the code in a code module and run it.

Nunny · Sep 17th 2004

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.

xlite · Sep 17th 2004

hi Nunny,

place this code in the ThisWorkbook area in VBE

Code

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

HTH

xlite · Sep 17th 2004

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

Code

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

HTH

Dovrox · Sep 17th 2004

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.

Sandieslover · Sep 17th 2004

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.

Code

Dim Oldzoom As Variant
Option Explicit


Private Sub Worksheet_Activate()
Oldzoom = ActiveWindow.Zoom
ActiveWindow.Zoom = 150
End Sub


Private Sub Worksheet_Deactivate()
ActiveWindow.Zoom = Oldzoom
End Sub

Display More

Goodluck

Sandieslover

Nunny · Sep 17th 2004

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.

:thanx:

Barbarr · Sep 17th 2004

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

determining the zoom of the current sheet.

Participate now!

Share