Auto-size Full Screen

[danrosey]

Have challenging one here...


Created a file intended to be viewed across multiple screen sizes. The goal is to have this display as full screen as the default. The problem I'm running into is that there really isn't a way for me to format the sheets in a way that is viewed perfectly on every screen size. Some viewers will need to scroll, while others will need to zoom.


Is there a way to set up the sheets or workbook as a whole to auto format to fit the viewers' screen size?

 

[Hui]

Danrosey


You can put a small bit of VBA in the Worksheet Module of the Worksheet that you want this to occur on

Copy/paste this into the Worksheet Module of your Worksheet

[pre]

Private Sub Worksheet_Activate()
Range("A1:N35").Select 'Change address to suit
ActiveWindow.Zoom = True
Range("A1").Select 'Change address to suit
End Sub

[/pre]

 

[danrosey]

Hui - thanks. Can you explain how this should work? I've copied this into the module but it doesn't seem to make any impact.

 

[Luke M]

danrosey,


The code will get fired off when the sheet is activated, aka selected. The code then select a defined range and zooms the screen as much as possible but all of that range is still visible. Then, we just select a single cell for clean-up purposes.


Be careful to put the code into a Worksheet module, not just a regular module. As long as you havne't accidentally disabled events macros, you should see something happen as soon as you select a different workskheet and then come back to this sheet.

 

[Jordan]

Dan, this link provides more instruction if you require:


http://excelvbaprogramming.wordpress.com/2012/09/17/automatically-fit-your-spreadsheet-to-its-best-fit-for-most-monitors/


but it's essentially the same information as given above.

 

[Hui]

Danrosey


Copy the above code

Goto Excel + open your file

Goto VBA Alt F11

In the left hand pane find your file and double click it to show all the worksheets if not shown

Find the worksheet that you want to apply the code

Right click on it and select Show Code

On the right hand side there will be a large blank pane, paste the above code into there

Edit the ranges in the code as is applicable

Go back to Excel with Alt F11

Save the File as an Excel *.xlsm or *.xlsb file, BUT NOT AS a *.XLSX file


When you change to that page it will adjust to the range in the code


If you want to make the Range more accessible, change the one line in the code to

Range("myRng").Select

Then Set up a named Formula in Excel as: myRng = Sheet1!A1:H30

You can now change the Displayed range by going to the Name Mgr Ctrl+F3 and changing the range there