MsgBox SelectionChange define range

Question

I have written the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myValue As Variant
If Range("B22") = "Yes" Then
        myValue = InputBox("InsertInitialDetach")
        Range("C22").Value = myValue
    End If
End Sub

This code is meant to do the following thing: If I select Yes from a Yes/No drop down list, a message box appears and asks me to fill in a date. The problem is that even after I fill the date, whatever I do afterwards, the box keeps on appearing and asking for the date. If I move two cells down, for example, the popup will continue to ask me for a date. Please tell me what should I do to fix this error?

Answer 1

You are using the selectionChange event which triggers after any change in the area selected, if want to trigger on value changes use the change event

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myValue As Variant
    On Error GoTo ErrorOccured
    If Target.Address = "$B$1" And Target.Value = "Yes" Then
        myValue = InputBox("Insert initialDetach")
        Range("B2").Value = myValue
    End If
    ErrorOccured:
    'Do when value is not valid
    End Sub