Data validation



I am not a super excel user but will really appreciate all the help I can get.

I used drop down in cell F8 and F9 with the value "yes or No" ( meaning that a user can select either a yes or no in cell F8 and F9 to a question).

Is it possible to to disallow user from selecting a response or answer (yes or no) in F9 when the user selected a "yes" in F8?

Thank you




You would require VBA for that purpose, meaning the workbook will contain a macro and must therefore be saved as macro enabled. The effect you describe could not be achieved by means of worksheet functions.

The code below should be installed in the code sheet of the worksheet on which you wish to have the action. To find that sheet, Click Alt+F11 from the worksheet view. That will open the VB Editor window. Find the workbook in the Project Explorer pane on the top left and expand (click on +). In the List of Microsoft Excel Objects find the worksheet and double-click on it. Paste the code below into the blank pane on the right.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    ' 18 Nov 2017
    Application.EnableEvents = False
    With Target
        If .Column = 6 Then                         ' column 6 = "F"
            Select Case .Row
                Case 8
                    If StrComp(.Value, "yes", vbTextCompare) = 0 Then
                       Range("F9").Value = ""
                    End If
                Case 9
                    If StrComp(Cells(8, .Column).Value, "yes", vbTextCompare) = 0 Then
                        MsgBox "Please skip this answer.", _
                               vbInformation, "Invalid response"
                        Range("F9").Value = ""
                    End If
            End Select
        End If
    End With
    Application.EnableEvents = True
End Sub


Thank you for your response. Can you help with a step by step guide on how I can do that?

thank you
Wale0912 Nov 17, '17 at 8:29 pm
lol: Nice! Please look my my expanded answer above.
Variatus (rep: 813) Nov 17, '17 at 9:41 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login