Solved: switching between yes and no using data validation

[Pete]

See attached workbook
Hi Experts

Need a macro to automatically switch the value from yes to no in worksheet "Contributions" row K12:O12.....when the user enters into any one of the worksheet(s) "scenario 1 to 5".....the word Essential Position into cell c7....

So sequnce:

if the user enter Essential Position into cell c7 in Scenario 1 then the validation switch to Yes in row k12 worksheet "Contributions" and all other fields remain NO

if then the user switches by enter Essential Positon into cell c7 worksheet "Scenario 2" and now "Scenario 1" is not the Essential Position, then worksheet "Contributions" row L12 = Yes all others remain as NO...

[Bob Phillips]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Address = "$A$7" Then

Application.EnableEvents = False

With Worksheets("Contributions")

.Range("K12:O12").Value = "No"
Select Case Sh.Name

Case "Scenario 1": .Range("K12").Value = "Yes"
Case "Scenario 2": .Range("L12").Value = "Yes"
Case "Scenario 3": .Range("M12").Value = "Yes"
Case "Scenario 4": .Range("N12").Value = "Yes"
Case "Scenario 5": .Range("O12").Value = "Yes"
End Select
End With
End If

Application.EnableEvents = True
End Sub


This is workbook event code.

[Pete]

thanks for thr feedback just testing the vba