Not allow data to be entered beyond a specified row.
 

How do I format the spreadsheet to not allow data to be entered beyond a
specified row. I have a spreadsheet that I don't want data entered below row
201. I haven't been able to find this on any of the help searches.
--
Cassie

Not allow data to be entered beyond a specified row.
 

With Data Validation
Select all the worksheet (click the square where the row headers meet the
column headers; or use CTRL+A once or twice)
With everything selected: use Data Validation | Custom and in the dialog use
this formula
=ROW()<201
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Cassie" wrote in message
...
How do I format the spreadsheet to not allow data to be entered beyond a
specified row. I have a spreadsheet that I don't want data entered below
row
201. I haven't been able to find this on any of the help searches.
--
Cassie

Not allow data to be entered beyond a specified row.
 

Cassie,

Right click your worksheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 201 Then
Application.EnableEvents = False
MsgBox "Not allowed"
Application.Undo
Application.EnableEvents = True
End If
End Sub

Mike

"Cassie" wrote:

How do I format the spreadsheet to not allow data to be entered beyond a
specified row. I have a spreadsheet that I don't want data entered below row
201. I haven't been able to find this on any of the help searches.
--
Cassie

Not allow data to be entered beyond a specified row.
 

I have other data validations set in this worksheet. When I tried what you
suggested below I got a pop up saying "contains more than one type of
validation - erase current settings and continue?" Can I do as you show
below with other validations?
--
Cassie


"Bernard Liengme" wrote:

With Data Validation
Select all the worksheet (click the square where the row headers meet the
column headers; or use CTRL+A once or twice)
With everything selected: use Data Validation | Custom and in the dialog use
this formula
=ROW()<201
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Cassie" wrote in message
...
How do I format the spreadsheet to not allow data to be entered beyond a
specified row. I have a spreadsheet that I don't want data entered below
row
201. I haven't been able to find this on any of the help searches.
--
Cassie

Not allow data to be entered beyond a specified row.
 

Mike -

Thanks much. That did it!
--
Cassie


"Mike H" wrote:

Cassie,

Right click your worksheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 201 Then
Application.EnableEvents = False
MsgBox "Not allowed"
Application.Undo
Application.EnableEvents = True
End If
End Sub

Mike

"Cassie" wrote:

How do I format the spreadsheet to not allow data to be entered beyond a
specified row. I have a spreadsheet that I don't want data entered below row
201. I haven't been able to find this on any of the help searches.
--
Cassie