Automatic Screen Fit

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RickGreg - 24 Jun 2006 18:53 GMT

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.

Reply to this Message

Ken Johnson - 25 Jun 2006 00:51 GMT

> I have a wksht that is shared among several different users across different
> systems and platforms. Due to varying screen sizes, resolutions, etc. not
[quoted text clipped - 8 lines]
>
> 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

Reply to this Message

RickGreg - 25 Jun 2006 13:08 GMT

>> I have a wksht that is shared among several different users across different
>> systems and platforms. Due to varying screen sizes, resolutions, etc. not
[quoted text clipped - 38 lines]
>
> 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.

Reply to this Message

Ken Johnson - 25 Jun 2006 15:15 GMT

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

Reply to this Message

RickGreg - 25 Jun 2006 16:37 GMT

> Is there a way I can also de-select those cells, and
>> instead select my first input cell (say, "C2")?
[quoted text clipped - 12 lines]
>
> Ken Johnson

Thanks again Ken. Works like a charm. -Rick

Reply to this Message

RickGreg - 25 Jun 2006 17:09 GMT

> Is there a way I can also de-select those cells, and
>> instead select my first input cell (say, "C2")?
[quoted text clipped - 12 lines]
>
> 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

Reply to this Message

Ken Johnson - 25 Jun 2006 22:31 GMT

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

Reply to this Message

Mac Forum / Applications / Excel / June 2006

Automatic Screen Fit