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.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,118
Office Version
  1. 2016
Platform
  1. Windows
One thing that might work is by sinking the application events from the personal workbook which should override the existing selection_change code in the other workbooks .


Put the following code in the ThisWorkbook module of your Personal workbook:

Code:
Option Explicit

Private WithEvents app As Application

Private Sub Workbook_Open()
    Set app = Application
End Sub

Private Sub app_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If ActiveWindow.Zoom <> 100 Then ActiveWindow.Zoom = 100 [B][COLOR=#008000]'<== or 125 or whatever[/COLOR][/B]
End Sub

Obviously, the code won't take effect until it the above Workbook_Open event code has ran.
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Can you please expand more about the "If Environ("username")" as I'm not familiar with it. Also, I use Mac ...
I do not use a Mac so sorry, I am unable to assist further.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,207
Messages
5,600,321
Members
414,376
Latest member
NickYOW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top