Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Range updating automatically as new data is entered | Charts and Charting in Excel | |||
the value of a box in excel if entered data is a certain value | Excel Worksheet Functions | |||
?-Change data series range as data is entered? | Charts and Charting in Excel | |||
Why won't my newly entered data sort with old data ? | Excel Discussion (Misc queries) | |||
Why won't my newly entered data sort with old data ? | Excel Discussion (Misc queries) |