Make Cells Required using IF statement

I'm setting up a spreadsheet and need to make cells in column B to require data in Date format if the adjacent cell in column A is not blank.
Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PhotographerCommented:
The simplest option would be yo use Data->Validation and set the cells in Column A to require date format.
0027.JPG
0
Author Commented:
KCTS, this is helpful, but I need column B to require a Date ONLY if column A has data.
0
Commented:
A date is just a number >= 0, and when cellformat is date, it is shown as a date.
You could format column B as date, and use conditional formatting to mark cells that require input, or has wrong input.
Mark the range B2:B10 (adjust to your need) and write formula for cell B2 in the conditional format.

First condition =AND(ISBLANK(A2)=FALSE,ISBLANK(B2))
Format yellow to indicate input needed.

Second condition =OR(ISTEXT(B2),AND(ISBLANK(A2),ISBLANK(B2)=FALSE))
Format red to indicate wrong input, text or input in B and no input in A

Third condition =AND(ISNUMBER(B2),B2<41275)
or use =AND(ISNUMBER(B2),YEAR(B2)<2013)
Format red to indicate date is before year 2013
0
Commented:
Reconsidering, datavalidation could be used to prevent user input when not allowed (cell in A is blank).
Use condition 'greater than' and this formula in the data validation
=IF(ISBLANK(A2),DATE(9999,12,31),DATE(2012,12,31))

If combined with the first condition for conditional format above, you will get at mark for input needed, and required date when needed.
0
Author Commented:
hgholt,

Thanks for info, but I really failed to explain what I need, so let me try to be a little more clear in what I'm looking for.

I need the user to get an error if they try to save/print the document if the required cells are not filled in.

The users are preparing a transmittal for boxes that are going to be barcoded stored in a warehouse.  Column A will show the barcode # and column B will show a Review Date.  We want the error to appear if there is a row with a barcode #, but no review date.

Thanks Again.
0
Commented:
That is only possible with VBA.
File must be saved as an xlsm type.

Insert below code in the Workbook module.
Change ActiveSheet in the function to Worksheets("Name of the sheet with barcodes").
Or delete other sheets.
If not, changing sheet will allow saving the file

Above data validation and conditional formatting can help finding cells with missing dates.

``````Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If DataControl = 1 Then
Cancel = True
MsgBox "Dates mising, print not allowed", vbCritical
End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If DataControl = 1 Then
Cancel = True
MsgBox "Dates mising, save not allowed", vbCritical
End If
End Sub

Function DataControl()
Dim RowNo As Long, ws As Worksheet
RowNo = 0
DataControl = 0
Set ws = ActiveSheet
Do
RowNo = RowNo + 1
If Len(ws.Cells(RowNo, 1)) > 0 Then
If Len(Cells(RowNo, 2)) = 0 Then
DataControl = 1
End If
End If
Loop Until RowNo > ws.UsedRange.Rows.Count Or DataControl = 1
End Function
``````
0
Author Commented:
hgholt,

I like where we are headed with this.  I added the formula in the data validation and its working.  However, having an issue with the VBA code.  I copied the code into the workbook module, changed "ActiveSheet" to "Barcodes" (the name of the worksheet) and saved it but it is still allowing the user to add data to A3, leave B3 blank and save the sheet.

I will mention that there are two header rows so the first row that the user is entering data is row 3.

Any further ideas?

Thanks.

Tim
0
Commented:
Did you accept the macro to run when the workbook was opened?
Change the settings to accept all macros in the security centre.
For your user as well, to ensure functionality.

Remove the Ignore blanks option in the Data validation.
Be sure that the data validation formula is using A3, and not A2.
Same issue if you use conditional format.

Change RowNo=0 to RowNo=2 in the function, to start on row 3.
It does not matter if all headers A1:B2 are used, since the function is not looking at the contents of the cells, only if there is something in B when A is used.

Example in attached workbook.
The marking is only to show where there are data validation and conditional format in column B.
Prevent-print-or-save.xlsm
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Thanks hgholt!  Not sure why but I still couldn't get this to work on my sheet.  I did, however, open your attachment and used that.  It's doing what I need it to.

Thanks again!
0
Author Commented: