Prevent rows from being selected

  • Thread starter Thread starter markb
  • Start date Start date
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.
 
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.
 
Back
Top