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, Try this.

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

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

enjoy, Rick


"Kees Korver"
wrote in message
...

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,
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"
wrote in message
...

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

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, 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"
wrote in message
...

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

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

Rick Hansen Wrote:
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
------------------------------------------------------------------------
Kees Korver's Profile: http://www.excelforum.com/member.php...o&userid=21562
View this thread: http://www.excelforum.com/showthread...hreadid=532165