Prevent rows from being selected

M

markb

I am trying to prevent users from selecting the first 9 rows and row 318 in a
worksheet. Protecting the sheet will not work, because the user needs to be
able to change the width of columns and do other worksheet functions. The
following code only tests for the active cell:

activeRow = ActiveCell.row

If activeRow = 1 Or activeRow = 2 Or activeRow = 3 Or activeRow = 4 Or
activeRow = 5 Or activeRow = 6 Or activeRow = 7 Or activeRow = 8 Or activeRow
= 9 Then
MsgBox ("Sorry, you can not select Rows 1 through 9.")
Range("A10").Select
End If

if activeRow = 318 then
msgbox("Sorry, you can not select Row 318.")
Range("A10").Select
End If

The problem, is that if the user starts in rows 10 or greater and selects
up, they can select into the first 9 rows because the active cell is the
first cell they select. Also, they can start a cell selection at cell 317 and
select through 318. Either of these actions allows for the changing of the
cell data. How can I prevent the selection of these rows?

Thank you for any help you can provide.
 
D

Dave Peterson

if not (intersect(selection,rows("1:9")) is nothing) _
or not (intersect(selection, rows(318)) is nothing) then
msgbox "you've selected the wrong rows!"
end if

If you're using a _selectionchange event, you'd want to use Target instead of
Selection.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top