Forum Discussion

Copper Contributor

May 06, 2022

Solved

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 & Banki...

excel

Formulas and Functions

Macros and VBA

HansVogelaar
May 06, 2022
EMBBerwyn

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 Sub

Switch 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.
Sample.xlsm14 KB

HansVogelaar

MVP

May 06, 2022

EMBBerwyn

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 Sub

Switch 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.

Sample.xlsm14 KB

EMBBerwyn

Copper Contributor

May 06, 2022

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.