MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Using "ScrollArea"


Posted by Stan on October 20, 2001 9:21 AM

I am using the following to limit the scroll area on a worksheet.

Sheets("MySheet").ScrollArea = "A1:A1"

This results in cell A1 being selected. In effect, I don't want any part of the worksheet to be accessible. I tried .ScrollArea = "", but this didn't work. Any suggestions?

Stan


Posted by Robert Charlton on October 20, 2001 2:47 PM


The code you are using should prevent any scrolling.

What do you mean by "I don't want any part of the worksheet to be accessible" ? (Everything hidden? Sheet protected?)

Posted by Stan on October 21, 2001 7:01 AM

I want the worksheet to be shown but the user cannot access any of the cells. The code above allows the user to access cell A:1. In fact, I've applied a background and cell A1 shows as being highlighted (I want to avoid this).

Stan

Posted by Tom Urtis on October 21, 2001 7:22 PM

Is this what you want?

Is your goal to have no cell appear as if it's selected? And/Or to not even let the user click on a cell to activate it or make it look activated? If so:

(1) Format the cell range for protection (or maybe the whole worksheet if that's what you want).

(2) Protect the sheet (I assume you have already done these first 2 steps).

(3) Go into the VBE and find your worksheet listed in your workbook's VBA Project of the Project Explorer (this is normally displayed in the left vertical pane of the VBE).

(4) Select/Highlight your sheet name, then click on View > Properties Window, and for the EnableSelection property, select "xlNoSelection"

(5) Close out of the VBE and you are good to go. Remember, this requires that your cells are formatted for protection and that the worksheet is actually protected.

Tom Urtis

Posted by Stan on October 22, 2001 5:05 AM

That did the trick - thanks!

(1) Format the cell range for protection (or maybe the whole worksheet if that's what you want). (2) Protect the sheet (I assume you have already done these first 2 steps). (3) Go into the VBE and find your worksheet listed in your workbook's VBA Project of the Project Explorer (this is normally displayed in the left vertical pane of the VBE). (4) Select/Highlight your sheet name, then click on View > Properties Window, and for the EnableSelection property, select "xlNoSelection" (5) Close out of the VBE and you are good to go. Remember, this requires that your cells are formatted for protection and that the worksheet is actually protected. Tom Urtis