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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

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.
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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?


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

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
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!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.