Boundaries Excel Worksheet

[Kees Korver]

Dear all, in one of my applications I would like to physically limit the number of rows and columns. So, the user cannot navigate beyond - say - 20 rows and 10 columns. Any way to do this in VBA? Thanks!

[Rick Hansen]

Kees, Try this.

Worksheets("Sheet1").ScrollArea = "A1:J20"

this will lock the worksheet at 20 rows, and 10 Columns.

enjoy, Rick


"Kees Korver" <[email protected]>
wrote in message
news:[email protected]...
>
> Dear all, in one of my applications I would like to physically limit the
> number of rows and columns. So, the user cannot navigate beyond - say -
> 20 rows and 10 columns. Any way to do this in VBA? Thanks!
>
>
> --
> Kees Korver
> ------------------------------------------------------------------------
> Kees Korver's Profile:
http://www.excelforum.com/member.php...o&userid=21562
> View this thread: http://www.excelforum.com/showthread...hreadid=532165
>

[NickHK]

Kees,
As Rick pointed out, it's .ScrollArea.
But this is not a persistent setting (in Office 2K anyway), so you have to
set it each time the WB is opened.

NickHK

"Kees Korver" <[email protected]>
wrote in message
news:[email protected]...
>
> Dear all, in one of my applications I would like to physically limit the
> number of rows and columns. So, the user cannot navigate beyond - say -
> 20 rows and 10 columns. Any way to do this in VBA? Thanks!
>
>
> --
> Kees Korver
> ------------------------------------------------------------------------
> Kees Korver's Profile:
http://www.excelforum.com/member.php...o&userid=21562
> View this thread: http://www.excelforum.com/showthread...hreadid=532165
>

[Kees Korver]

Nick (or others), how can I accomplish this automatically? If I open a Workbook, can I force Excel to run a macro immediately? e.g. with Workbook_Open command somewhere?
Just make a macro in VBA-editor with the name Workbook_Open()?
(I tried this, but Excel doesn't seem to detect this)
Thanks!
Kees

[QUOTE=NickHK]Kees,
As Rick pointed out, it's .ScrollArea.
But this is not a persistent setting (in Office 2K anyway), so you have to
set it each time the WB is opened.

NickHK

[Rick Hansen]

Kees, here is the procedure to insert your code Workbook_Open().

First goto the VB Editor, now from VBA Project Explore window right
click on "ThisWorkbook", now click on "view code". Now on the left side
above the edit window click on the drop list and select "Workbook", now
from the right side drop down click and select the event. In your case
select "Open". Now VBE will place skeleton structure of Workbook_Open()
sub into the edit window. Now add the code to this sub ie...

Private Sub Workbook_Open()

Worksheets("Sheet1").ScrollArea = "A1:J20"

End Sub

enjoy, Rick



"Kees Korver" <[email protected]>
wrote in message
news:[email protected]...
>
> Nick (or others), how can I accomplish this automatically? If I open a
> Workbook, can I force Excel to run a macro immediately? e.g. with
> Workbook_Open command somewhere?
> Just make a macro in VBA-editor with the name Workbook_Open()?
> (I tried this, but Excel doesn't seem to detect this)
> Thanks!
> Kees
>
> NickHK Wrote:
> > Kees,
> > As Rick pointed out, it's .ScrollArea.
> > But this is not a persistent setting (in Office 2K anyway), so you have
> > to
> > set it each time the WB is opened.
> >
> > NickHK
>
>
> --
> Kees Korver
> ------------------------------------------------------------------------
> Kees Korver's Profile:
http://www.excelforum.com/member.php...o&userid=21562
> View this thread: http://www.excelforum.com/showthread...hreadid=532165
>

[Kees Korver]

Rick, all works fine! Thank you for your attention and time! Kees

[QUOTE=Rick Hansen]Kees, here is the procedure to insert your code Workbook_Open().

First goto the VB Editor, now from VBA Project Explore window right
click on "ThisWorkbook", now click on "view code". Now on the left side
above the edit window click on the drop list and select "Workbook", now
from the right side drop down click and select the event. In your case
select "Open". Now VBE will place skeleton structure of Workbook_Open()
sub into the edit window. Now add the code to this sub ie...

Private Sub Workbook_Open()

Worksheets("Sheet1").ScrollArea = "A1:J20"

End Sub

enjoy, Rick