Checking and changing Zoom value without Activating workbook/sheet?

[mcomp72]

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.

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?

 

[JLGWhiz]

Did you try

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.

 

[JLGWhiz]

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

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]

I tried your suggestion of

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?