Data Validaiton For Null Value, Dicated By Combo Box

[dtadams]

Hello,

I wish I could provide a screen shot, but I am new to the forum and am not sure exactly how to properly display my image.

Issue:
I have a Prepare by Section (cell C8) and a Date Completed Section (Cell C9) and I am trying to force the user to fill out these two fields based upon their selection from the combo box which is located in cell B12. The valid options for the combo box are Yes, No, Waiting, and NR.

Ideal Solution:
I am looking for help writing a formula that essentially says "If ComboBox = "Yes" Then Prepared By and Date Completed <> Null. If any of the other selctions are made, the Date Completed and Prepare By Cells could remain blank. I have been trying different varitions of the If Function and IsBlank, but unfortunetly I can not seem to figure out how to properly word this within Excel.

I hope that this is descriptive enough and thank you for any help in advance !

 

[SlinkRN]

Hi DT,
Is this combobox on a userform or are you talking about a dropdown in data validation on a sheet? Slink

 

[dtadams]

Hi SlinkRN,

The dropdown is based on a validation list :Yes, No, Waiting, NR as the valid options. Sorry for calling the control a combo box when in reality it is a validation list, sorry again for any confusion. Thanks again for the help!

Regards,
Dtadams

 

[SlinkRN]

Hi again,
OK, I think I have what you need. Go to the workbook code (click on Alt F11 and then right click on "Workbook" and click on "View Code"). Now paste this code in that window.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ActiveSheet.Range("B12") = "Yes" Then
    If ActiveSheet.Range("C8") = "" Or ActiveSheet.Range("C9") = "" Then
    MsgBox "Must fill in cells C8 and C9 before closing if answer is yes in cell B12", vbOKOnly, "Insufficient Data"
    Cancel = True
    End If
End If
End Sub

Now if you're user tries to close the workbook before entering the required data it will stop them. You probably want to change the wording of the messagebox to make it more friendly to the user (not using cell references but labels of the kind of data they are missing). In the future you might want to consider using a userform to have users enter data. You can do much more controlling of data entry if you use a userform. Let me know if this works for you! HTH, Slink

 

[dtadams]

Thank You So Much!

I can not tell you how long I have been trying to figure that out! I have been trying some extremely tedious formulas ,when VBA was the answer. The solution worked great and thank you so much for the help!

Regards,
Dan

 

[SlinkRN]

Thanks for the feedback Dan - it was my pleasure Glad it worked for you! Slink