Limit working area of a sheet

[Guest]

Hi,
We have a spreadsheet which limits the available cells you can enter values
into - basically A1 to N40. If you show column headers these are not shown
either for rows/columns outside that range.
I wish to know where this is set so that I can do it on other sheets, but do
not know how.
Does anyone have any ideas?
Many thanks.
Andy

 

[Rob Bovey]

We have a spreadsheet which limits the available cells you can enter
values
into - basically A1 to N40. If you show column headers these are not shown
either for rows/columns outside that range.
I wish to know where this is set so that I can do it on other sheets, but
do
not know how.
Does anyone have any ideas?

Hi Andy,

The only way I'm aware of to not display row and column headers beyond a
certain area is to hide all rows below that area and hide all columns to the
right of that area. Select them all and choose Format/Row/Hide and
Format/Column/Hide from the menu.

You can limit the area that the user has access to without hiding all
those rows and columns by adding the a startup macro similar to the
following to your workbook, changing the name of the worksheet as
appropriate.

Sub Auto_Open()
Sheet1.ScrollArea = "$A$1:$N$40"
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Guest]

Rob,
Many thanks for your post. The first method you listed (format/row/hide etc)
did the trick. I couldn't actually get the auto_open macro to work, but then
again maybe it doesn't work in Excel 97 (which I forgot to point out).

Many thanks for your help.

Andy

 

[Tom Ogilvy]

the auto_open macro must be placed in a general/standard module (the type
you get when you do Insert=>Module in the VBE).

See Chip Pearson's page on Events

http://www.cpearson.com/excel/events.htm

Auto_Open works in versions from xl5 to xl2003 although in xl97 and later
you might want to use the workbook_Open event in the ThisWorkbook module.