Listbox autoselects item if openned under cursor via cell doubleclick

[user0]

Hi guys,
I have experience some weird excel behavior here..

I have userform with list that opens from sheet via doubleclick on cell with data (highlighted yellow).
On open userform:
1) move userform to the center of excel window
2) populates listbox from named range
3) (optional step) selects listbox item if it matched Target's cell value. This step can be omitted, it does not affect this "bug", but I left it here just to show my further desired action that was scred up by this weird excel behavior.

Private Sub UserForm_Initialize()
   'center userform
    Top = Application.Top + (Application.UsableHeight / 2) - (Height / 2)
    Left = Application.Left + (Application.UsableWidth / 2) - (Width / 2)
    Call populate_lstTest
End Sub

Private Sub populate_lstTest()
    With lstTest
        .Visible = False
        .ColumnCount = 2
        .ColumnWidths = "100,100"
        .RowSource = ["tblData"]
        Call Listbox_ScrollAndSelectItem(lstTest, CurrentCellValue)
        .Visible = True
    End With
End Sub

Function Listbox_ScrollAndSelectItem(ByRef lst As Object, ByVal t As String)
Dim i As Integer
    With lst
        For i = 0 To .ListCount - 1
            If .List(i) = t Then
                .Selected(i) = True
                .TopIndex = i
                Exit For
            End If
        Next
    End With
End Function

But for some reason it automatically selects listbox item that appears under cursor (if userform popups right above doubleclicked cell).
It looks like there is some extra phantom click on listbox right after userform has shown.

To reproduce - narrow excel window (see picture) and try to open userform via doubleclick, make sure that userform popups under cursor.

 

[user0]

..it turned out that VBA was creating an empty click event subroutine (Private Sub lstTest_Click()) each time I went through the code. I would delete this empty subroutine, and it would just go and create it again the next time I ran the code. On top of that, each time I went used the debugger, it would force the debugger to step through this newly created empty sub. Maybe this is standard behaviour, but I've not seen it before (but then that doesn't mean a whole lot...).

Thank you for the deep look into the issue.
Now I'm pretty sure this is Excel's bug.

In terms of a workaround, I've found that getting the system to pause through a loop (with DoEvents) will serve as a bandaid (of sorts).

Nice workaround, it works, thank you!

 

[Dan_W]

Turns out its not just you who has experienced this problem!

Double Click Loads Userform, but Userform Somehow Inherits the Clicks

I have run into some bug-like behavior in Excel 2010 that is driving me absolutely crazy. I can't find a way around it, and I can't find any mention of it online. A userform is activated by double-clicking cells in certain specific columns on a worksheet. On the userform is a listbox with...

www.mrexcel.com

Programatic ListBox selection is selecting the wrong item

I am building an Excel VBA project which makes use of a ListBox to navigate through a tree structure. By double clicking an item it will expand below with additional items. My goal is that by makin...

stackoverflow.com

Double-Click Listbox Selection Problem

Hello all. I am running into the following problem and cannot find a solution. I have a Userform with 2 Listboxes. The Userform initializes with Listbox1 visible and Listbox 2 not visible. My

answers.microsoft.com