limit worksheet size

[AdrianaMex]

I have created a doc in excel, but I don't want users (or myself) to scroll
down through the hundereds of empty cells below my two pages. Or scroll right
across the hundreds of empty cells.

How can I restrict the size of a work sheet to prevent this scrolling?

Even trying to hide columns and rows seems too hard.
Basically, my first sheet is a one-pager.
The second- and following sheets will have one column but may grow several
rows.
Thanks in advance!
Adriana

 

[JP]

Try freezing rows to keep your users on the data cells. Just select
the cell below and to the right of the end of your data, then click
Window>Freeze Panes. For example if your data was in cells A1 through
G10, click cell H11.


HTH,
JP

 

[Mike Rogers]

AdrianaMex

You might try going to tools>Options>View tab and remove the check marks
from "Horizontal scrool bar" and Vertical scroll bar". This way they have
less to work with. They can still scrool with the arrow keys but may be less
likely to do so.

Mike Rogers

 

[Tyro]

If they wish to waste their time doing so, let them

Tyro

 

[AdrianaMex]

Hi JP and Mike, First, thank you for your answears, but this is not what I am
looking for. I've seen others' sheets work like this, but I don't know how
people do it. Could it be that they create a form?
Some times I've received letters that in stead of being in word are written
in excel.
And you don't see the extra columns and rows....
Regards,

 

[Roger Govier]

Hi

You can do it by setting the scroll area of a page.
To try this, right click on a tab and choose View Code
If the Properties window is not visible, press F4 to activate it.
3 rows from the bottom, you will see a ScrollArea property.
Type in there A1:K50, then press Alt+F11 to return to Excel

You will now find that you can only move your cursor within that range that
you set.

If that is what you want, then you will need to add some code to the
Workbook Open event of your Workbook, to set the values, as they are only
transient, and are "forgotten" as soon as Excel closes.

Press Alt+F11 to return to the VBE, and double click on the ThisWorkbook
module in the left pane of explorer.
Copy the code below and Paste into the white pane on the right of the
screen.
Private Sub Workbook_Open()

ThisWorkbook.Worksheets("Sheet1").ScrollArea = "A1:K50"

End Sub

Change the sheet name and the scroll area to suit.

Be aware that this method will prevent the user from doing other things,
e.g. Insert or Delete Rows or Columns.
You can remove the scroll area at any time by using the manual method
described at the beginning of this post, and just deleting the values in the
Scroll Area field.

In code, just set ScrollArea =""

 

[AdrianaMex]

Thank you, Roger -
I was expecting something more on the line of defining the print area as a
work area.

 

[Gord Dibben]

These "letters" you received were in the form of an Excel workbook file?

Or just the one page?

Through code you can limit the scroll area of the sheet.

Since the scrollarea method does not stick between sessions you will have to
reset it each time.

You may wish to place the code into a WorkBook_Open Sub in ThisWorkbook module
and specify which worksheet if only one sheet required.

Private Sub WorkBook_Open()
Sheets("YourSheet").ScrollArea = "A1:J20"
End Sub

Change "YourSheet" to the actual name of the sheet in your workbook.

Or also in the Thisworkbook module to limit scrollarea on all sheets.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
With ActiveSheet
.ScrollArea = "A1:J20"
End With
End Sub

Right-click on the Excel Icon left of "File" on the menu bar and slect "View
Code"

Copy/paste your choice of subs from above into that module.

Save the workbook and Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

 

[JP]

The print area is just that -- the part of the worksheet that will
print.

If you had some idea of the solution you wanted, you have a obligation
to let us know instead of letting us make suggestions so you can
reject them.

--JP