Jun 08 2022 11:42 AM
Jun 08 2022 11:42 AM
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.
Jun 08 2022 12:11 PMSolution
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
Jun 08 2022 12:18 PM