Stop data entry into cell with condition

andonny · Apr 9th, 2002, 03:19 AM

Hi,
I would like to stop anybody to enter anything into B1 if A1 does not have anything in it. If A1 has an entry then I should be able to enter data into B1.

How do I achieve this.

Thanks for your help in advance
Andonny

Qroozn · Apr 9th, 2002, 03:36 AM

why do you want to do it?
can you run a macro at the end that says if a cell i blank that put in a certain value?

Derek · Apr 9th, 2002, 03:38 AM

Hi there

Use a custom validation with this formula=OFFSET(B1,0,-1)<>""

Put this in the Error Alert
"Entry is only allowed when A1 is not blank"
Regards
Derek

andonny · Apr 9th, 2002, 03:44 AM

Hi,
I am not too sure where and how to put the alert

Thanks a million
Andonny

Brian from Maui · Apr 9th, 2002, 03:49 AM

From the Data Validation window, click on the Error Alert tab

andonny · Apr 9th, 2002, 04:33 AM

Hi,
Great this works perfectly. Is it also possible to stop an entry all together. This way it is still possible to make an entry if the alert is ignored. I am happy with this solution but just in case somebody wants to take it a step further.

That would be down the the whole column B.

Your help is very much appreciated
Andonny

Derek · Apr 9th, 2002, 06:45 AM

Hi again
In data validation, on the ERROR ALERT tab you need a tick in the box against "Show error alert after invalid data is entered" and in the Style drop down box you need to select STOP. This will prevent an entry succeeding.

With data validatiion already in B1 just select column B, go to data validation and go OK to extending the validation to other cells.

Be aware, however, that validation is a type of cell formatting and that it can be circumvented by copying a non-validated cell and pasting it over your validated cell.

regards
Derek

Derek · Apr 9th, 2002, 07:09 AM

Andonny

An alternative to data validation is an event macro.

Right click your sheet name tab, left click View Code and paste this code into the white area. Use Alt+F11 to return to your sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Target.Column = 2 Then
If Target.Offset(0, -1) = "" Then
MsgBox "You cannot enter data in Column B if the cell in Column A is blank"
Target.Offset(0, 1).Select
End If
End If
End Sub

You will get fewer unnecessary messages if you also go to Tools, Options, Edit and change the setting of "Move selection after Entry" so that the Direction is Right (rather than Down)

Good Luck
Derek

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 9th, 2002, 03:19 AM	#1
andonny Join Date: Mar 2002 Posts: 217	Hi, I would like to stop anybody to enter anything into B1 if A1 does not have anything in it. If A1 has an entry then I should be able to enter data into B1. How do I achieve this. Thanks for your help in advance Andonny

Apr 9th, 2002, 03:36 AM	#2
Qroozn Join Date: Mar 2002 Location: Sydney/Brisbane , Australia Posts: 539	why do you want to do it? can you run a macro at the end that says if a cell i blank that put in a certain value?

Apr 9th, 2002, 03:38 AM	#3
Derek Join Date: Feb 2002 Location: Perth Australia Posts: 1,564	Hi there Use a custom validation with this formula=OFFSET(B1,0,-1)<>"" Put this in the Error Alert "Entry is only allowed when A1 is not blank" Regards Derek

Apr 9th, 2002, 03:44 AM	#4
andonny Join Date: Mar 2002 Posts: 217	Hi, I am not too sure where and how to put the alert Thanks a million Andonny

Apr 9th, 2002, 03:49 AM	#5
Brian from Maui Join Date: Feb 2002 Posts: 7,617	From the Data Validation window, click on the Error Alert tab

Apr 9th, 2002, 04:33 AM	#6
andonny Join Date: Mar 2002 Posts: 217	Hi, Great this works perfectly. Is it also possible to stop an entry all together. This way it is still possible to make an entry if the alert is ignored. I am happy with this solution but just in case somebody wants to take it a step further. That would be down the the whole column B. Your help is very much appreciated Andonny

Apr 9th, 2002, 06:45 AM	#7
Derek Join Date: Feb 2002 Location: Perth Australia Posts: 1,564	Hi again In data validation, on the ERROR ALERT tab you need a tick in the box against "Show error alert after invalid data is entered" and in the Style drop down box you need to select STOP. This will prevent an entry succeeding. With data validatiion already in B1 just select column B, go to data validation and go OK to extending the validation to other cells. Be aware, however, that validation is a type of cell formatting and that it can be circumvented by copying a non-validated cell and pasting it over your validated cell. regards Derek

Apr 9th, 2002, 07:09 AM	#8
Derek Join Date: Feb 2002 Location: Perth Australia Posts: 1,564	Andonny An alternative to data validation is an event macro. Right click your sheet name tab, left click View Code and paste this code into the white area. Use Alt+F11 to return to your sheet. Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error Resume Next If Target.Column = 2 Then If Target.Offset(0, -1) = "" Then MsgBox "You cannot enter data in Column B if the cell in Column A is blank" Target.Offset(0, 1).Select End If End If End Sub You will get fewer unnecessary messages if you also go to Tools, Options, Edit and change the setting of "Move selection after Entry" so that the Direction is Right (rather than Down) Good Luck Derek