200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hello,
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