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?
Ejgil HedegaardConnect With a Mentor 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.
Brian PiercePhotographerCommented:
The simplest option would be yo use Data->Validation and set the cells in Column A to require date format.
RavenTimAuthor Commented:
KCTS, this is helpful, but I need column B to require a Date ONLY if column A has data.
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Ejgil HedegaardCommented:
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.

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
Ejgil HedegaardCommented:
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 combined with the first condition for conditional format above, you will get at mark for input needed, and required date when needed.
RavenTimAuthor Commented:

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.
Ejgil HedegaardCommented:
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
        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

Open in new window

RavenTimAuthor Commented:

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?


RavenTimAuthor 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!
RavenTimAuthor Commented:
Very helpful, very responsive!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.