Forum Discussion
2 Data Validation Queries and Students Changing Their Minds
I have a two cells using data validation.
A1 Question “Are you an Economics major ?” Data Validation List “Yes/No”
A2 Question “Are you enrolled in Money & Banking 203?” Data Validation List “Yes/No”
Based upon answers to the above queries different class options are displayed. If a student initially answers “Yes” to both queries and later changes their response to A1 to “No ”, how can I automatically change the response to A2 to “” while leaving the Data Validation list unchanged for A2. A student may change their answer to A1 back to “Yes”, and change their response to A2 from “” to “Yes” or “No”
Right-click the sheet tab.
Select 'View code' from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("A1"), Target) Is Nothing Then If Range("A1").Value <> "Yes" Then Application.EnableEvents = False Range("A2").ClearContents Application.EnableEvents = True End If End If End SubSwitch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm).
Students will have to allow macros when they open the workbook.
Sample workbook attached.
4 Replies
Right-click the sheet tab.
Select 'View code' from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("A1"), Target) Is Nothing Then If Range("A1").Value <> "Yes" Then Application.EnableEvents = False Range("A2").ClearContents Application.EnableEvents = True End If End If End SubSwitch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm).
Students will have to allow macros when they open the workbook.
Sample workbook attached.
- Hans,
I've run into a problem, students are answering "Yes" to both questions and then changing their major while leaving the Money & Banking answer as "Yes," since seats are limited how can I hide both the second question and the data validation box if or when they change their majors?
Thanks,If a student changes A1 to "No" or clears A1, the code should automatically clear A2. But it's easy to hide row 2 - see the attached version.
- Perfect, I learned that you could insert VBA code directly into the worksheet. I was always inserting VBA code in Module#.
Hans, first let me thank you using some of your time to solve my problem. I appreciate the prompt response.
Time is our only asset.
