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.

 

[Jaafar Tribak]

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:

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.

 

[Peter_SSs]

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.