Automatic Screen Fit

[RickGreg]

I have a wksht that is shared among several different users across different
systems and platforms. Due to varying screen sizes, resolutions, etc. not
all users can see the full intended screen view (cells a1:H24, named
"Introscreen") without making zoom adjustments.

I created a macro "fitintroscreen" which uses the zoom:view selection
command, but I was hoping there was some simple VBA code I could add to the
page Code so that it automatically executed each time that tab was opened.

Alas, I do not know VBA! Can anyone help with some code?

Many thanks.

 

[Ken Johnson]

I have a wksht that is shared among several different users across different
systems and platforms. Due to varying screen sizes, resolutions, etc. not
all users can see the full intended screen view (cells a1:H24, named
"Introscreen") without making zoom adjustments.

I created a macro "fitintroscreen" which uses the zoom:view selection
command, but I was hoping there was some simple VBA code I could add to the
page Code so that it automatically executed each time that tab was opened.

Alas, I do not know VBA! Can anyone help with some code?

Many thanks.

Hi Rick,

This worked for me...

Private Sub Worksheet_Activate()
Me.Range("A1:H4").Select
Application.ActiveWindow.Zoom = True
End Sub



Copy the code then right-click the introductory sheet's tab and select
"View Code" then paste the code into the code module. Then press Alt +
F11 to return to normal Excel.

Also, if the Introductory sheet is the first sheet that appears when
the workbook is open you will need similar code in the ThisWorkbook
code module so that it auto-zooms on opening. If this is the case then
the following worked for me...

Private Sub Workbook_Open()
Application.ActiveSheet.Range("A1:H4").Select
Application.ActiveWindow.Zoom = True
End Sub


Copy the code, right-click a worksheet tab then select "View Code".
Look for the ThisWorkbook icon (has a green X) in the Project Explorer
then double click it. Paste the code into the module that then appears.
Press Alt + F11 to return to normal Excel.



Ken Johnson

 

[RickGreg]

Hi Rick,

This worked for me...

Private Sub Worksheet_Activate()
Me.Range("A1:H4").Select
Application.ActiveWindow.Zoom = True
End Sub



Copy the code then right-click the introductory sheet's tab and select
"View Code" then paste the code into the code module. Then press Alt +
F11 to return to normal Excel.

Also, if the Introductory sheet is the first sheet that appears when
the workbook is open you will need similar code in the ThisWorkbook
code module so that it auto-zooms on opening. If this is the case then
the following worked for me...

Private Sub Workbook_Open()
Application.ActiveSheet.Range("A1:H4").Select
Application.ActiveWindow.Zoom = True
End Sub


Copy the code, right-click a worksheet tab then select "View Code".
Look for the ThisWorkbook icon (has a green X) in the Project Explorer
then double click it. Paste the code into the module that then appears.
Press Alt + F11 to return to normal Excel.



Ken Johnson

Thanks Ken. That works perfectly. One more question. Your code leaves
"A1:H4" selected. Is there a way I can also de-select those cells, and
instead select my first input cell (say, "C2")?

Thanks again.

 

[Ken Johnson]

Is there a way I can also de-select those cells, and

instead select my first input cell (say, "C2")?

Hi Rick,

Silly me, I didn't notice that!

Private Sub Worksheet_Activate()
Me.Range("A1:H4").Select
Application.ActiveWindow.Zoom = True
Me.Range("C2").Select
End Sub


will fix that up.

Ken Johnson

 

[RickGreg]

Is there a way I can also de-select those cells, and

Hi Rick,

Silly me, I didn't notice that!

Private Sub Worksheet_Activate()
Me.Range("A1:H4").Select
Application.ActiveWindow.Zoom = True
Me.Range("C2").Select
End Sub


will fix that up.

Ken Johnson

Thanks again Ken. Works like a charm. -Rick

 

[RickGreg]

Is there a way I can also de-select those cells, and

Hi Rick,

Silly me, I didn't notice that!

Private Sub Worksheet_Activate()
Me.Range("A1:H4").Select
Application.ActiveWindow.Zoom = True
Me.Range("C2").Select
End Sub


will fix that up.

Ken Johnson

Each answer brings up another idea/question! Your first response suggested
adding code for the Workbook so the first screen appears properly upon
opening. What if someone saves the file while on another sheet (so that
that sheet appears upon re-opening the file)? Can I add a line of code to
the This Workbook Code Module that will automatically make the intended
Worksheet open?

In other words, if I want sheet1 to appear on opening, and the user saves
such that sheet3 appears, how can I correct that?

Thanks again, I appreciate all your help.
-Rick

 

[Ken Johnson]

Hi Rick,

In other words, if I want sheet1 to appear on opening, and the user saves
such that sheet3 appears, how can I correct that?

This works OK...

Private Sub Workbook_Open()
Me.Sheets(1).Activate
Application.ActiveSheet.Range("A1:H4").Select
Application.ActiveWindow.Zoom = True
ActiveSheet.Range("C2").Select
End Sub


However, if a user is able to change the order of the sheet tabs and
saves that change then it will no longer be your intro sheet that opens
first.
So, a better way may be to use the intro sheet's name. The code below
assumes that name is "Intro" (it could still be 'Sheet1" for all I
know), so just edit the code to the correct name.

Private Sub Workbook_Open()
Me.Worksheets("Intro").Activate
Application.ActiveSheet.Range("A1:H4").Select
Application.ActiveWindow.Zoom = True
ActiveSheet.Range("C2").Select
End Sub

Also, some of the changes that users can make can be eliminated using
Workbook protection.


Ken Johnson