Range.Delete causes unexpected Click event

[CPerdue]

Good, bleary-eyed, morning folks,

Today's conundrum is that using Range.Delete(xlUp) is causing unexpected _Click events (two in fact) in a ListBox.

I have a named range, "List", covering $A$3:$A$7 which is used in the box's ListFillRange property and as data in the Sub UpdateList. A second range, "Destination" is at $K$3:$M$4. The ListBox itself is physically at ~$C$3:$D$5. Finally, a count of times the ListBox1_Click() runs is kept at $D$11.

Private Sub ListBox1_Click()
    Range("$C$11").Value = ListBox1.Value
    Range("$D$11").Value = Range("$D$11").Value + 1
End Sub
Public Sub UpdateList()
    Application.EnableEvents = False
    For i = 1 To 5
        Range("Destination").Resize(1, 1).Offset(1 + i, 2).Value = Range("List")(i)
    Next i
 
    Range("Destination").Resize(10, 1).Offset(0, 2).Find("c").Delete (xlUp)
    Application.EnableEvents = True
End Sub

I've set a breakpoint at the _Click, when I get there and look at the call stack I see:

VBAProject.Sheet1.ListBox1_Click
Non-Basic Code (with a format that makes drives this window nuts)
VBAProject.Sheet1.UpdateList

The "Application.EnableEvents = False" didn't seem to help.

I've seen similar things happen with CheckBox_Click, that is a different post I suppose.

Thanks much,

C.

 

[AlphaFrog]

Maybe you could use the _MouseDown event instead of the _Click event.

Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Range("$C$11").Value = ListBox1.Value
    Range("$D$11").Value = Range("$D$11").Value + 1
End Sub

Also on an unrelated issue, the (xlUp) isn't the correct syntax. It shouldn't have the parenthesis, and it should be xlShiftUp (though both xlConstants are actually the same value).

Range("Destination").Resize(10, 1).Offset(0, 2).Find("c").Delete [COLOR="Red"]xlShiftUp[/COLOR]

 

[CPerdue]

Huh, must be some equivalence for old syntax...anyway, it works the same.

I'm not actually ever clicking on the ListBox.

Just tested

Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Range("$e$11").Value = ListBox1.Value
    Range("$f$11").Value = Range("$f$11").Value + 1
End Sub

... it works and does NOT go off unexpectedly.

OK, this is a valid workaround but geeze, I wonder what is actually happening?

Thanks for the help.