Resetting data validation questions to the default of blank.

[EMBBerwyn]

I'm creating a health questionnaire for students some of whom are veterans. I've created a little form to help identify their health insurance coverage and next of kin. A copy of which has been uploaded.

The questionnaire starts with the query "Do you have health/medical insurance?"

If the answer is "Yes", a series of questions will be displayed. For each question there is a data validation list of possible answers. Other than Medicare answers are either "Yes" or "No."

Frequently, students and many others are unsure of their health insurance. They may answer incorrectly and later wish to correct their answers. For example, a veteran may believe that Tricare is their primary health insurance. However, by law Tricare only covers approved medical claims after all other insurance claims have been paid. Hence, they may wish to change their answer to the question "Is Tricare your primary health insurance?" from "Yes" to "No'."

However, some students start over with the first question, "Do you have health/medical insurance?" In that event all the data validation questions must return to the default answer which is a blank. I have written some code that hides all the questions and returns the answers to all questions to the default blank when the answer to the first question is changed to "No."

However, if they change their minds and click on "Yes" or "-" after selecting "No", all their previous answers appear in the data validation cells.

I need to reset all data validation cells to blank if they change their answer to the first question.

I've tried a couple different Sub routines but when executed they were "$#^^^&^"

I've uploaded a workbook "Student Health Insurance."

Any suggestions would be deeply appreciated.

[#2 · Accepted solution]

@EMBBerwyn 

Use this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B2"), Target) Is Nothing Then
        If Range("B2").Value <> "Yes" Then
            Application.EnableEvents = False
            Range("B6:D6,B8,B9,B11,B14").ClearContents
            Application.EnableEvents = True
        End If
    End If
    If Not Intersect(Range("B8"), Target) Is Nothing Then
        If Range("B8").Value <> "Yes" Then
            Application.EnableEvents = False
            Range("B9").ClearContents
            Application.EnableEvents = True
        End If
    End If
End Sub

[#3]

Hans,

Thank you for such a prompt response to my question. I've tested your VBA code several times seems to work perfectly. Thank you very much.

Ed