Stop data entry into cell with condition

andonny

Board Regular
Joined
Mar 11, 2002
Messages
220
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Hi Derek,

I am trying to perform this same task but with the added condition that cell B1 must be populated with specified text in addition to cell A1 being empty. Using the word 'Empty' as an example of the text that must be displayed byB1, how would I update the VBA to capture this added condition?

Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top