Avoid user to access first two rows in a sheet

A

Arvi Laanemets

Hi

Why don't you use simply sheet protecting for this. Uncheck Locked checkbox
on Format.Cells.Protection tab for all cells the user is allowed to change,
and check it for cells you want to protect, and after that set sheet
protection on.
 
J

Jürgen

To protect the contents of the 2 first rows of an excel sheet, so that
the user cannot change them, I wrote the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row <= 2 Then Cells(3, 1).Select
End Sub

When the user selects a cell or a range which overlaps one of the
first two rows the cell A3 is selected and thus the user cannot change
the contents of the first two rows, I thought :eek:(

Even with this code the user can change the contents by a double-click
on the header of row 1 or 2 or on a column header.

The code I wrote for this was build up of more than one line in the
beginning, so I thought that a double-click was fast enough to be
interpreted between two lines of the code, but even with this single
line it doesn't work.

I also tried to add Application.Interactive = False but it doesn't
change anything.

All suggestions are welcome!
 
R

Rob Bovey

Hi Jürgen,

The best way to handle this would be to select all cells on the
worksheet that you want the user to be able to change, select
Format/Cells/Protection from the menu, uncheck the Locked check box, then
choose Tools/Protection/Protect Sheet and enter a password. By doing this,
Excel will automatically prevent any changes to cells that were not
unlocked.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
 
K

kkknie

Why not just protect the worksheet.

First select all cells choose Cells from the Format window.
Under the Protection tab, uncheck the Locked checkbox.
Now select the first two rows and choose format Cells again.
Check the locked checkbox.
Now from Tools menu, select Protection | Protect Sheet.
Select a password if you wish.

Now the users will not be able to change the first two rows of th
spreadsheet.
 
G

gr8guy

Hi Friend,

Try "Scrollarea" property of the worksheet Object & specify the range as starting from row 3 down, excluding the 1st 2 rows. for eg:
Private Sub Worksheet_Activate()
Sheets("Sheet1").ScrollArea = "A3:IV65536"
End Sub

to remove scrollarea later when needed, change code:

Sheets("Sheet1").ScrollArea = ""


So this way user will not be able to access the 1st 2 rows.

OR the other option is to password protect the 1st 2 rows (as ranges)
Goto Tools >
Protection >
Allow Users to Edit Ranges >
Select Range as 3rd row till row 65536 & till end of last column IV (use Cntrl+right arrow key for selection) > give a Password > Give Permissions to Users TO ALLOW/DENY, who can access the sheet > also protect the sheet.

whenever Users click the any rows below 1 & 2 rows, they will have to supply the correct password. 1st 2 rows will be protected due to sheet protection.

Hope these are useful!

Rgds,

Eijaz
 
J

J?rgen

Hi Guys,

Many thanks for the feedback.

It is thru that there are some alternatives to have a similar
functionality but I am still wondering why it is still possible to
select an entire row or column by doubleclick on the row/column label.
I would like to understand why this happens since the code to control
the selection is only one line so I suppose it is impossible to break
the execution of this line in two parts?

It would also be nice to have an explanation why adding
application.interactive = false isn't a solution.


Many thanks!

Jürgen.
 
Top