Restrict Scroll Area

A

anurag.d

Hello,
I want to restrict the scroll area in a sheet.
To do so I made the change in the sheets Properties box.
However,once i save the file and close it and open it again, the
restriction is gone.
Can anyone suggest a way to retain the restriction.

Thanks,
Anura
 
W

William

Place the code below in the "ThisWorkbook" module of the relevant workbook.
Amend sheet name and range as appropriate.

Private Sub Workbook_Open()
With ThisWorkbook.Sheets("Sheet1")
..ScrollArea = "A1:p50"
End With
End Sub


--
XL2002
Regards

William

[email protected]

| Hello,
| I want to restrict the scroll area in a sheet.
| To do so I made the change in the sheets Properties box.
| However,once i save the file and close it and open it again, the
| restriction is gone.
| Can anyone suggest a way to retain the restriction.
|
| Thanks,
| Anurag
|
|
| ---
|
|
 
W

William

Email me the file explaining what sheets you want the scroll area restricted
on and what the scroll range should be for each sheet. Please don't post the
file on this ng.

--
XL2002
Regards

William

[email protected]

| Hello,
| I tried but it doesnt seem to work.Can u help fast!!
|
| Rgds,
| Anurag
|
|
| ---
|
|
 
A

anurag.d

William,
I appreciate ur help,however the data in the sheet is confidential.
If u can mail me a sample blank sheet it will be appreciated.
My mail id is:[email protected].

Anyone else having a better idea plzzz help!!

Thanks
fellas..
 
D

Dave Peterson

What do you mean about not working?

Things that could have gone wrong:

You have to close (and save) this workbook (with the additional routine). The
reopen the workbook.

Macros have to be enabled.
Answer yes to the macro prompt--and even verify that macros are not disabled.

tools|macro|Security|Security Level
(medium will cause a prompt, low will open with macros enabled and no prompt)

Are you sure you put it under the ThisWorkbook module?

Did you change the name of the worksheet to match your workbook and did you
adjust the range to match what you want.

When I did all those things, William's code worked for me.
 
W

William

Hi Dave

The OP did get it working after I mailed him a blank file with the code. I
asked him to respond to the ng (as shown below) to that effect so people
like yourself wouldn't waste their time. He didn't oblige unfortunately.

--------------
Anurag
Glad to help.
Please can you post your comments to the newsgroup so the thread is closed
off, others can benefit and other people will not waste their time in
attempting to resolve your query which has now been dealt with.
--------------

--
XL2002
Regards

William

[email protected]

| What do you mean about not working?
|
| Things that could have gone wrong:
|
| You have to close (and save) this workbook (with the additional routine).
The
| reopen the workbook.
|
| Macros have to be enabled.
| Answer yes to the macro prompt--and even verify that macros are not
disabled.
|
| tools|macro|Security|Security Level
| (medium will cause a prompt, low will open with macros enabled and no
prompt)
|
| Are you sure you put it under the ThisWorkbook module?
|
| Did you change the name of the worksheet to match your workbook and did
you
| adjust the range to match what you want.
|
| When I did all those things, William's code worked for me.
|
| "anurag.d <" wrote:
| >
| > Hello,
| > I tried but it doesnt seem to work.Can u help fast!!
| >
| > Rgds,
| > Anurag
| >
| > ---
| >
|
| --
|
| Dave Peterson
| [email protected]
 
D

Dave Peterson

Thanks for closing the loop.
Hi Dave

The OP did get it working after I mailed him a blank file with the code. I
asked him to respond to the ng (as shown below) to that effect so people
like yourself wouldn't waste their time. He didn't oblige unfortunately.

--------------
Anurag
Glad to help.
Please can you post your comments to the newsgroup so the thread is closed
off, others can benefit and other people will not waste their time in
attempting to resolve your query which has now been dealt with.
--------------

--
XL2002
Regards

William

[email protected]

| What do you mean about not working?
|
| Things that could have gone wrong:
|
| You have to close (and save) this workbook (with the additional routine).
The
| reopen the workbook.
|
| Macros have to be enabled.
| Answer yes to the macro prompt--and even verify that macros are not
disabled.
|
| tools|macro|Security|Security Level
| (medium will cause a prompt, low will open with macros enabled and no
prompt)
|
| Are you sure you put it under the ThisWorkbook module?
|
| Did you change the name of the worksheet to match your workbook and did
you
| adjust the range to match what you want.
|
| When I did all those things, William's code worked for me.
|
| "anurag.d <" wrote:
| >
| > Hello,
| > I tried but it doesnt seem to work.Can u help fast!!
| >
| > Rgds,
| > Anurag
| >
| > ---
| >
|
| --
|
| Dave Peterson
| [email protected]
Click to expand...
 
D

Dave Peterson

Could you unlock the cells you need to modify, and protect the sheet.

Then you could have a macro that runs that sets the scrollarea (including both
ranges), but only allows selection of the unlocked cells.

It might be kind of close to what you want--put it in your auto_open or
workbook_open event:

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi"
.EnableSelection = xlUnlockedCells
.ScrollArea = .Range("a1:f22").Address
End With
End Sub

Excel won't remember these settings after you close it and reopen the workbook
(that's why it's in auto_open).
 
A

anuradha

Hey there

This is Anu and I am working on a project in which i need to export
data from excel to access. I tried the code given in this site using
DAO and was unsuccessfull. Can u help me in this regard. I have written
a program in VBA and the cursor is not reading any data base through the
set property. I will be very greatfull if you can help me in this
regards.

Have seen many posts from you. I hope u will login regularly and answer
my query ASAP

Regards
Anu. :)
 
Top