Limit Scrollarea

[exalan]

How can I limit scrollarea say, from A1 to G50 only. i tried the following
macro, press F4 to state Scrollarea to be A1:G50. However, each time after
i've saved and re-open the file, the setting of Scrollarea in the property
(press F4) is gone and therefore the limit scrollarea is not working:

Private Sub Scroll_Area()
Worksheets("Sheet1").ScrollArea = "A1:G50"
End Sub


Please help....

 

[Dan R.]

Just put the following in 'ThisWorkbook':

Private Sub Workbook_Open()
Worksheets("Sheet1").ScrollArea = "A1:G50"
End Sub

 

[Gaurav]

why dont you hide all the columns after G and all the rows after 50?

or select H51 and freeze panes?

 

[Tom Hutchins]

The scrollarea method does not persist between sessions, so you will have to
reset it each time you open the workbook. You may wish to place this code
into a WorkBook_Open sub in the ThisWorkbook module:

Private Sub WorkBook_Open()
Sheets("Sheet1").ScrollArea = "A1:G50"
End Sub

Hope this helps,

Hutch

 

[exalan]

Thanks for the advice, just that I don't want file user to unfreeze/unhide &
see my detailed computation....

 

[exalan]

Hi Gaurav & Hutchins

Thanks for advice. However, tried your method but somehow, it still didn;t
work.

 

[Gaurav]

well...that can be prevented by protecting the sheet after hiding the
columns/rows.

 

[Tom Hutchins]

Try this alternate code:

Right-click the name tab of the sheet where the scroll area should be
limited. Select View Code from the menu that pops up. You will be taken to
the Visual Basic Editor (VBE), and the code page for that worksheet will be
displayed. Enter the following code:

Private Sub Worksheet_Activate()
Me.ScrollArea = "A1:G50"
End Sub

Press Alt-Q to leave the VBE. Save your workbook. This code should limit the
scroll area for that sheet only whenever that sheet is activated.

Hutch

 

[Gord Dibben]

If you don't want users to see your computations, send them a workbook with
static values and the computations bits deleted.

Excel worksheet protection is meant to protect inadvertant erasure of formulas
and stuff.

Not reliable for hiding data from determined users.


Gord Dibben MS Excel MVP

 

[exalan]

Hi Gaurav

Many thanks for the suggestion.

 

[exalan]

Hi Tom

Many thanks for the advice...
--
exalan

Try this alternate code:

Right-click the name tab of the sheet where the scroll area should be
limited. Select View Code from the menu that pops up. You will be taken to
the Visual Basic Editor (VBE), and the code page for that worksheet will be
displayed. Enter the following code:

Private Sub Worksheet_Activate()
Me.ScrollArea = "A1:G50"
End Sub

Press Alt-Q to leave the VBE. Save your workbook. This code should limit the
scroll area for that sheet only whenever that sheet is activated.

Hutch

 

[exalan]

Hi Dan

Many thanks for the advice..
--
exalan

Just put the following in 'ThisWorkbook':

Private Sub Workbook_Open()
Worksheets("Sheet1").ScrollArea = "A1:G50"
End Sub