Macro to specify zoom level for the workbook

[excel_stuff]

Hi listers,

I want to include a box (or a cell) in my front sheet so the user can specify the zoom level for the entire workbook. I have come across the following macro which lets you zoom to a specified percentage but not exactly what I am looking for.

Sub SetZoom()Dim ws As Worksheet

ForEach ws In Worksheets
ws.Select
ActiveWindow.Zoom =85 //change as per your requirements
Next wsEndSub

I would appreciate your help.

Thank you!
ExcelStuff

 

[Deepak]

Pls check with attached.

 

[excel_stuff]

Thanks, Deepak. This is excellent and works perfect on your workbook!

Can I ask a silly question: how do I import this to my workbook? I am more familiar with writing or copy/pasting sub routines directly into my file but this one doesn't seem to work in this way. Also would I need to run this one? Sorry this is ridiculously basic question but I would appreciate your help.

Also, is it possible to limit the range that they could put in the cell (say between 70 and 100)?

Thanks again
ExcelStuff

 

[Deepak]

Not an issue!!
Copy to Sheet~Right Click on sheet name>View Code then copy paste the below code there.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet, w As Worksheet
Application.ScreenUpdating = False
Set w = ActiveSheet
    If Target.Address = "$A$1" Then
        For Each ws In Worksheets
            ws.Select
            ActiveWindow.Zoom = Sheet1.[A1].Value '//change as per your requirements
        Next ws
    End If
w.Activate
Application.ScreenUpdating = True
End Sub

if u wish to manually run the code then copy paste the below code in a module

Sub SetZoom()
Dim ws As Worksheet, w As Worksheet
Application.ScreenUpdating = False
Set w = ActiveSheet
        For Each ws In Worksheets
            ws.Select
            ActiveWindow.Zoom = Sheet1.[A1].Value '//change as per your requirements
        Next ws
w.Activate
Application.ScreenUpdating = True
End Sub

To limit add this to up of Application.ScreenUpdating = False

If Sheet1.[A1] < 70 Or Sheet1.[A1] > 100 Then Exit Sub

 

[excel_stuff]

Excellent Deepak!! Totally spot on. Thank you so much for your help.

Much appreciated!
ExcelStuff

 

[Deepak]

Welcome!!!!