Checking and changing Zoom value without Activating workbook/sheet?

mcomp72

Board Regular
Joined
Aug 14, 2016
Messages
157
I have a question about reading & setting the zoom level of a worksheet, but I need to explain exactly what I'm trying to do.

In my workbook, I'm writing code to allow the user to import data from another workbook of the same type. (This is in case the version they have been working on becomes corrupted and/or the formatting gets messed up. Then they can import all their data into a new file where the formatting is okay.)

The user may have attached images to the "source" workbook, and so I want to copy those images to the "target" workbook. (The target workbook is ThisWorkbook.) I was able to get that to work properly... most of the time.

I have found that if the zoom level of the source sheet does not match the zoom level of the target sheet, when I copy the images from one to the other, they don't appear in the correct place on the target sheet. So I have written code to check the zoom level of each sheet, and if they are not equal, it will make them equal.

Here's my problem: when it checks the zoom level, and when it changes it (if it needs to), the screen flashes between the source and target workbooks, even though I have Application.ScreenUpdating equal to False. Is there any way to check/change the zoom level without first activating the workbook/worksheet I want to read/change?

Here's how I am currently checking the zoom level. This code resides in a function called RecordZoomValue. WBtoUse is a variable of type Workbook that it assigns, depending on which workbook it is currently checking. SheetName is a string variable.

Code:
With WBtoUse.Sheets(SheetName)

    .Activate
    RecordZoomValue = ActiveWindow.Zoom

End With
The code works fine; I'm just trying to avoid the screen flashing between the two workbooks. The .Activate line seems to be the culprit, but I don't know how to use ActiveWindow.Zoom without first activating the sheet. When the sheet in the source workbook is activated, the screen flashes. Then when I activate a sheet in ThisWorkbook, it flashes again. Is there anything I can do to stop that?
 
Last edited:

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,932
Office Version
2013
Platform
Windows
Did you try
Code:
RecordZoomValue = WBtoUse.Sheets(SheetName).Zoom
?
Although I am pretty sure you have to activate the sheet to make it the active window. Zoom is for the Window, not the sheet.
 
Last edited:

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,932
Office Version
2013
Platform
Windows
I am wondering if it might be the resolution difference more than the zoom. For the zoom, you could just set the zoom for the active sheet, whatever it is. In other words, when a different sheet becomes active just do
Code:
ActiveWindow.Zoom = 100
but if the screen resolution is different, the zoom will not help. And that is a problem with Excel graphics. After all, it is not a graphics application, it is primarily a number cruncher.
 

mcomp72

Board Regular
Joined
Aug 14, 2016
Messages
157
I tried your suggestion of

Code:
RecordZoomValue = WBtoUse.Sheets(SheetName).Zoom
but I received an error: Object doesn't support this property or method

As to your other reply, you may be right, it could be the resolution that is the problem -- I don't know enough about how Excel works to know one way or the other. But what I do know is that upon Activating another workbook, the screen changes to show that workbook, even though I have ScreenUpdating set to False, and I would really love for it not to do that. It makes my workbook feel "amateurish" by having the screen flash like that. (My workbook will be used by many different people, most of whom I won't know personally.)

Anyone know of a way to stop the screen from changing when activating another workbook?
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,365
Messages
5,468,191
Members
406,570
Latest member
Ktvu2006

This Week's Hot Topics

Top