How to restrict Zoom in and out (Disable Zooming)

[nagovind]

Dear all

I need a macro to diable zooming in or out for a defined sheet/ active sheet

please help

 

[Luke M]

In the ThisWorkbook module of the VBE, put something like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

ActiveWindow.Zoom = 100

End Sub




You're not [b]really[/b] disabline the zoom so much as you are forcing it to be a constant of your choosing. Alternatively, you could select the specific worksheet module and use this:



Private Sub Worksheet_Change(ByVal Target As Range)

ActiveWindow.Zoom = 100

End Sub

 

[Hui]

@Luke

This doesn't stop Zooming, just resets it to 100% when you change a value

Ctrl Scroll still zooms in/out

 

[Luke M]

@Hui,


Good catch, I guess I jumped the gun on that one. Looks like there's no truly robust way of disabling every zoom method...perhaps the better question is why you'd want it disabled? This post seemed very similar:

http://www.sqldrill.com/excel/programming-vba-vb-c-etc/330311-how-disable-zoom-excel-vba.html

 

[nagovind]

Dear Mr. Hui


You have already posted the link once regarding the chart tool from xl rotor


Brian Murphy at

xlRotor http://www.xlrotor.com/excel_stuff.htm


in that the zooming is disabled after selecting the chart but reading through the codes i'm unable to get the exact codes


This is required because i have a sheet designed with many objects/ shapes which has link to many macros if it is zoomed < 100% the objects are becoming too small and making the user confused to select the proper icons

 

[Luke M]

@nagovind

Here's another shot. In the worksheet module, place these 2 macros:

Private Sub Worksheet_Activate()

Call ZoomChange

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)

Call ZoomChange

End Sub




That should call our working macro whenever we need it (although you could easily add other worksheet events if desired. Then, in a regular module, put this code:



Sub ZoomChange()

Dim dTime As Date

Dim iCount As Integer


dTime = Now

Application.OnTime dTime + TimeValue("00:00:01"), "ZoomChange"


iCount = iCount + 1

ActiveWindow.Zoom = 100


If iCount = 5 Then

iCount = 0

Application.OnTime dTime + TimeValue("00:00:01"), "ZoomChange", , False

End If


End Sub

If the user tries to change the zoom level, this should force it back to 100% zoom.

 

[nagovind]

@Luke M


Marvelous

thanks

it is like virtual Zoom disable

 

[nagovind]

Luke M

This is good macro

But it is becoming a virus sorry

If u place this code in a separate worksheet and save then open and try to close it

if i try to close it is againg opening

if i'm wrong i regrett for that

 

[Luke M]

Yikes, that's no good!

Okay, let's change the one macro into 2 seperate ones, the latter will be out "stop" switch.

Dim dTime As Date

Dim iCount As Integer


Sub ZoomChange(Optional xKill As Boolean)


dTime = Now

iCount = iCount + 1

ActiveWindow.Zoom = 100

Application.OnTime dTime + TimeValue("00:00:01"), "ZoomChange"


End Sub

Sub StopZoom()

Application.OnTime dTime + TimeValue("00:00:01"), "ZoomChange", , False

End Sub




Now, in the ThisWorkbook module, we'll activate the stop switch when we close the workbook like this:




Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call StopZoom

End Sub

Sorry about the "virus" caused by first macro. Hopefully this one works out.

 

[nagovind]

great

Thanks Luke M

 

[SHEELA9489]

LUKE M

I GOT GREAT SOLUTION

BUT

THIS WORKS IN ALL WORKSHEETS OPEND

SIR

PLS GIVE SOLUTION LIKE

EVEN IF I OPEN 10 WORK BOOKS

ZOOMSTOP SHOULD WORK IN ONE WORKBOOK INSIDE ONE SHEET ONLY


CAN U GIVE SOLUTION FOR ME