Set Excel to look the same on all monitor resolutions

[AndyMan81]

Hi all,

Ok, I have finally finished my Excel project, however the challenge I now face is to make the Excel document look the same on all monitors.

For example, I have a laptop that I doc when I am in the office. When I view the file when the laptop is doc'd (on monitors), the file looks perfect. However when I view the file on my acutal laptop screen I need to play around with the zoom to allow the file to fit properly.

Has anyone got a VBA code I can use?

I need to show columns A to Y, and rows 1 to 32.

Many thanks,

Andy.

[Olly]

Try:

Sub zoomfoo()
Dim c As Range
Set c = Selection
Range("A1:Y32").Select
ActiveWindow.Zoom = True
c.Select
End Sub

[AndyMan81]

Hi Olly,

That works great! I do however have another problem though. The "Home Page" is set to the above, however the worksheet has about 10 tabs which all require their own setting. Is there a way I can apply the above code to each tab (and amend the range)?

[Olly]

Yup. Use this, and amend the sheet / range references as needed:

Sub zoomfoo(rng As Range)
rng.Worksheet.Activate
rng.Select
ActiveWindow.Zoom = True
rng.Cells(1, 1).Select
End Sub

Sub wszoomfoo()
Dim c As Range, ws As Worksheet
Set c = Selection
Set ws = ActiveSheet

Application.ScreenUpdating = False

'Amend this section, to specify all your sheets / ranges
Call zoomfoo(Worksheets("Sheet1").Range("A1:Y32"))
Call zoomfoo(Worksheets("Sheet2").Range("A1:E10"))
Call zoomfoo(Worksheets("Sheet3").Range("A1:AZ60"))

ws.Activate
c.Select
Application.ScreenUpdating = True
End Sub

[AndyMan81]

Brilliant!!

How do I get it to run automatically? I already have code in the file, I have just entered this at the bottom.

I would like this to run when it opens on each different PC.

[Olly]

In ThisWorkbook module, use:

Private Sub Workbook_Open()
Call wszoomfoo
End Sub