Macro to zoom to fit window, then apply that zoom factor to other sheets

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,258
Hi all.

I have set up a workbook that is sent out to lots of different users. They each keep and use their own copy.

I have set it up so that everything looks OK and is visible on MY screen, but I'm conscious that some users may have different screen sizes, different toolbars set up, and so on, which might make some parts not immediately visible to them.

I have set up an auto-execute macro which automatically sets the zoom factor to best fit, for several of the worksheets, and this works fine.
Here's the code that does it.
Code:
Sheets("WELCOME").Select
    Range("A1:N18").Select
    ActiveWindow.Zoom = True
By repeating this code for each worksheet, I can make each one be zoomed just right.

However, the file contains 8 sheets that are all identically laid out, except the number of rows is different.
What I want to do is go to the worksheet that has the largest number of rows (it's always the same worksheet, so I know which one it is), set the zoom factor for THAT worksheet (which I can do, and it always has the same number of rows), and then take THAT zoom factor, whatever it is - and it will vary depending on the user - and apply that to the other worksheets that have a similar layout.

I could just go through each worksheet and zoom it automatically, but that would mean that some of the sheets looked very large, others very small, and I'd like them to have a consistent appearance.
I could also specify a range on each worksheet that was similar to the appropriate range on the longest worksheet, and zoom that automatically, but that's not ideal either, because some of the row heights vary from sheet to sheet, and again I'll end up with different font sizes.

Anyone know how to do this ?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

cornflakegirl

Well-known Member
Joined
Nov 4, 2004
Messages
2,023
After you've auto-zoomed, set a variable eg iZoom = ActiveWindow.Zoom

Then on other sheets just set ActiveWindow.Zoom = iZoom
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,258
Thanks very much cornflakegirl.

I should have also said that I know virtually nothing about macros - most of the ones I use are built up using the recorder, and I don't really understand what the coding does.

I've tried doing what you suggested but couldn't get it to work.

Here's the code
Code:
Dim Izoom As ActiveWindow.Zoom
    Sheets("COLLABORATE").Select
    Set ActiveWindow.Zoom = Izoom
I'm sure I'm making a very stupid mistake here - can you tell me what it is please ?
 

cornflakegirl

Well-known Member
Joined
Nov 4, 2004
Messages
2,023
Um... not sure what you're doing with your Dim statement...

Something like:

Code:
Dim iZoom as integer
Sheets("WELCOME").Select
Range("A1:N18").Select
ActiveWindow.Zoom = True
iZoom = ActiveWindow.Zoom
 
Sheets("COLLABORATE").Select
ActiveWindow.Zoom = iZoom
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,258
Um... not sure what you're doing with your Dim statement...

Well like I said, I really don't know what I'm doing ! :)

But thanks very much - your code works perfectly !
 

cornflakegirl

Well-known Member
Joined
Nov 4, 2004
Messages
2,023
My (limited) understanding of Dim statements is that they're just to tell the macro what type of variable you're creating - you can't do anything functional with them.

(Why the new disclaimer on your sig? Was there actual confusion? I really like the "another day" one btw - would have voted for it in the poll if it had been there :))
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Gerald,

This may be an option, but note that there might be some problems with Using Range depending on where you want to zoom.

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetActivate(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>)<br>    ActiveSheet.UsedRange.Select<br>    ActiveWindow.Zoom = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


Hope that helps,

Why the new disclaimer on your sig? Was there actual confusion?

Sadly, yes. ;)
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,258
Thanks cornflakegirl and pennysaver.

Cornflakegirl's suggestion does seem to do what I want.

I will take a look at pennysaver's as well, to see if it does something different.

Was there actual confusion about the signature ?
Yes indeed . . .
<TABLE style="WIDTH: 54pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=72 border=0 x:str><COLGROUP><COL style="WIDTH: 54pt" width=72><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=72 height=17>http://www.mrexcel.com/forum/showthread.php?t=314320</TD></TR></TBODY></TABLE>
 

Forum statistics

Threads
1,181,648
Messages
5,931,213
Members
436,784
Latest member
amuljono

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