ActiveWindow.Zoom - Need to change value but not sure how

newbe71

New Member
Joined
Mar 10, 2014
Messages
8
Hi Guys,

Hope you can help me with a very annoying problem.

I'm working on a project and receive around 1,000 .xlsm workbooks each week that I need to edit and save. The problem is the person who is sending these workbooks inserted a VBA code on each that goes like this:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range
Set r = Intersect(Target, Range("A47:P54"))
If r Is Nothing Then
ActiveWindow.Zoom = 80
Else: ActiveWindow.Zoom = 52
End If
End Sub

He is working from an old monitor and the screen resolution is not very high. That’s why the default worksheet opens is just 80 and drop down to 52 on certain range (so he can see everything without scrolling). I on the other hand work on an iMac which makes this very hard to read and its just too small. when I try to zoom in, then each time I switch cell it revert back to the 80 zoom level he defined. I would like to know how I can solve this.

I was thinking on the following and hope to know if its possible.

a) Create a simple macro that using a keyboard shortcut will change the values and set the zoom level to 125.

b) Not sure if its possible but maybe there is some sort of search-replace command (in batch) that will search the values (80 and 52) and replace them (all 1,000 files). That will solve the problem.

I’m opening to any ideas you have. I even tried to do a code that change the zoom level according to screen resolution but he is working on windows and I am on a mac system so that didn’t worked.

Thank you very much for all the help.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
630
Office Version
365, 2013
Platform
Windows
That won't be easy as your colleague used "Worksheet_SelectionChange" event. It means that every time you change a selection the Zoom will automatically adjust to either 80% or 52%.

a) Keyboard shortcut would help but only for a moment, as "Worksheet_SelectionChange" event is still active (i.e. you change your selection and zoom adjusts again). In any case, you can try to add this code:

Code:
Sub Zoom125()    
    With ActiveWindow
        .Zoom = 125
    End With
End Sub
...then go to Developer ribbon > Macros > select "Zoom125" > Options > assign a shortcut key

You can also add it to Quick Access Toolbar to make it accessible from any Excel file.
https://support.office.com/en-us/ar...a-button-728c83ec-61d0-40bd-b6ba-927f84eb5d2c

b) As far as I know, there is no search-replace command for VBA code that you could implement for 1,000 files. However, if "Worksheet_SelectionChange" is the only macro that exists in the file, then I suggest using a macro that would: 1. Open an Excel file, 2. Change zoom to 125, 3. Save file in .xlsx format (i.e. all macros will be deleted), 4. Loop & repeat for the remaining 999 files.
 

newbe71

New Member
Joined
Mar 10, 2014
Messages
8
That won't be easy as your colleague used "Worksheet_SelectionChange" event. It means that every time you change a selection the Zoom will automatically adjust to either 80% or 52%.

a) Keyboard shortcut would help but only for a moment, as "Worksheet_SelectionChange" event is still active (i.e. you change your selection and zoom adjusts again). In any case, you can try to add this code:

Code:
Sub Zoom125()    
    With ActiveWindow
        .Zoom = 125
    End With
End Sub
...then go to Developer ribbon > Macros > select "Zoom125" > Options > assign a shortcut key

You can also add it to Quick Access Toolbar to make it accessible from any Excel file.
https://support.office.com/en-us/ar...a-button-728c83ec-61d0-40bd-b6ba-927f84eb5d2c

b) As far as I know, there is no search-replace command for VBA code that you could implement for 1,000 files. However, if "Worksheet_SelectionChange" is the only macro that exists in the file, then I suggest using a macro that would: 1. Open an Excel file, 2. Change zoom to 125, 3. Save file in .xlsx format (i.e. all macros will be deleted), 4. Loop & repeat for the remaining 999 files.

Thank you very much for your reply.

Solution “A” will not work as I need to edit almost 40 different lines on each workbook.

Can we maybe create a macro to access the view code of the worksheet (it has the same worksheet name and exact lines of code on every workbook), then edit “ActiveWindow.Zoom = 80” and “Else: ActiveWindow.Zoom = 52” by simply replacing these values? This will work very good for me. Then I will simply use a shortcut to activate this macro, which will replace 80 to 125 and 52 to 120.

Can this be done? Create a macro to replace values on the worksheet code?

Thank you!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,712
Office Version
365
Platform
Windows
Try this. In your personal macro workbook put this macro. You can assign a keyboard shortcut if you want or add the macro to your Quick Access Toolbar.
You can use it to disable 'events' like the SelectionChange and set the zoom to whatever you want. Run the code again when finished & it will re-activate the event code so on the next selection change the appropriate zoom will be set.

Rich (BB code):
Sub Toggle_Events()
  If Application.EnableEvents Then
    Application.EnableEvents = False
    ActiveWindow.Zoom = 100 'or whatever you want
  Else
    Application.EnableEvents = True
  End If
End Sub
 
Last edited:

newbe71

New Member
Joined
Mar 10, 2014
Messages
8
Try this. In your personal macro workbook put this macro. You can assign a keyboard shortcut if you want or add the macro to your Quick Access Toolbar.
You can use it to disable 'events' like the SelectionChange and set the zoom to whatever you want. Run the code again when finished & it will re-activate the event code so on the next selection change the appropriate zoom will be set.

Rich (BB code):
Sub Toggle_Events()
  If Application.EnableEvents Then
    Application.EnableEvents = False
    ActiveWindow.Zoom = 100 'or whatever you want
  Else
    Application.EnableEvents = True
  End If
End Sub
WOW! Thank you so much! From some reason it doesn't work when I add this into the personal macro workbook (it might interfere with other functions) BUT I can ask the other person to add this macro into the workbook when he create the sheet and then it will work just fine!

Bless you!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,712
Office Version
365
Platform
Windows
.. some reason it doesn't work when I add this into the personal macro workbook
What are the symptoms of it not working? Does it throw an error, crash Excel, do nothing, something else?
I assume you did run it when you had one of those automatically zooming worksheets as the active sheet?

In any case, it sounds like you have influence or control over what code they have in their worksheets, If that is so, then this might be an even easier way.
Suppose your log in name is "Mickey Mouse" then you could have them use this SelectionChange code instead of what they are currently using.

That way, when you are using the sheet the zoom will always be your chosen value and when anybody else is using the sheet the zoom will be the usual 80/52.
Note that your user name is case-sensitive.

Rich (BB code):
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim r As Range
  
  If Environ("username") = "Mickey Mouse" Then
    ActiveWindow.Zoom = 100 ' or whatever you want
  Else
    Set r = Intersect(Target, Range("A47:P54"))
    If r Is Nothing Then
      ActiveWindow.Zoom = 80
    Else
      ActiveWindow.Zoom = 52
    End If
  End If
End Sub
 
Last edited:

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,845
Office Version
2016
Platform
Windows
If you don't need code on those workbooks, can you not just open them with macros disabled?
 

newbe71

New Member
Joined
Mar 10, 2014
Messages
8
What are the symptoms of it not working? Does it throw an error, crash Excel, do nothing, something else?
I assume you did run it when you had one of those automatically zooming worksheets as the active sheet?

In any case, it sounds like you have influence or control over what code they have in their worksheets, If that is so, then this might be an even easier way.
Suppose your log in name is "Mickey Mouse" then you could have them use this SelectionChange code instead of what they are currently using.

That way, when you are using the sheet the zoom will always be your chosen value and when anybody else is using the sheet the zoom will be the usual 80/52.
Note that your user name is case-sensitive.

Rich (BB code):
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim r As Range
  
  If Environ("username") = "Mickey Mouse" Then
    ActiveWindow.Zoom = 100 ' or whatever you want
  Else
    Set r = Intersect(Target, Range("A47:P54"))
    If r Is Nothing Then
      ActiveWindow.Zoom = 80
    Else
      ActiveWindow.Zoom = 52
    End If
  End If
End Sub
The script does work BUT it also disable other macros from some reason. I get no error, but seems like it disable other condition (like for example, we have a macro and condition to show a button after certain cells are filled and its not showing anymore).

Can you please expand more about the "If Environ("username")" as I'm not familiar with it. Also, I use Mac and the other person uses Windows, and I do know some actions are not working on Mac environment.

Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,099,574
Messages
5,469,475
Members
406,655
Latest member
pwilson06

This Week's Hot Topics

Top