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

[newbe71]

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.

 

[newbe71]

Any idea guys? This is quite urgent.

Thanks!

 

[JustynaMK]

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:

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]

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:

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]

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.

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

 

[newbe71]

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.

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]

.. 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.

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

 

[Jaafar Tribak]

If you don't need code on those workbooks, can you not just open them with macros disabled?

 

[newbe71]

If you don't need code on those workbooks, can you not just open them with macros disabled?

We have other macros on this sheet which I need them. So its not possible to disable them.

 

[newbe71]

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.

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!