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?

Selection Change is triggered when you change the selected range or cell, Are you sure you don't want to use Change instead? — Zoilo Reyes, Mar 21 '18 at 15:58

Vityata · Answer 1 · 2018-03-21T15:52:03.700

2

Would this be ok:

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

It checks every time whether you are changing Range("B22") and whether Range("B22") "Yes".

edited Mar 21 '18 at 15:52

answered Mar 21 '18 at 15:36

Vityata

42,633
8
55
100

Vityata, thank you for your help. I ran the code and I get this error message: "Object variable or With block variable not set". – RobertC Mar 21 '18 at 15:48
@RobertC - this is what happens when I write code and I do not test it. See the update. – Vityata Mar 21 '18 at 15:52
1

Indeed it works, Vityata. Thank you for your help and for making me understand the lack of the synthax in the initial code. – RobertC Mar 21 '18 at 15:57

Zoilo Reyes · Accepted Answer · 2018-03-21T17:22:03.823

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

edited Mar 21 '18 at 17:22

answered Mar 21 '18 at 16:05

Zoilo Reyes

573
4
9

Thank you, Zoilo Reyes. I have now understood my mistake. – RobertC Mar 21 '18 at 16:10
If you select `B1:B2` and press delete, the code explodes in error `13`. – Vityata Mar 21 '18 at 16:32

MsgBox SelectionChange define range

2 Answers2