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.

 

[Dan_W]

Call Listbox_ScrollAndSelectItem(lstTest, CurrentCellValue)

It doesn't appear from your code that you've assigned any value to the variable 'CurrentCellValue'. Try replacing it with Application.Activecell.Value.
So the line would read:

Call Listbox_ScrollAndSelectItem(lstTest, Application.ActiveCell.value)

I tried it and it seems to work.

 

[user0]

Sorry, I forgot to mention that CurrentCellValue is gllobal variable and assigned with Target.value2 on Worksheet_BeforeDoubleClick event.

My real issue is in slightly different matter, I have recorded short video to demonstrate "listbox autoselection".
To simplify things I have commented item selection code, so there must no selection at all in this video.
However, item autoselects when listbox popups under cursor (incorrect behavior).
If listbox popups not under cursor there are no any selection (as it should be).

https://imgur.com/IED7GMc

 

[Norie]

Why not use the row number of the clicked item to set what's selected in the listbox?

Since you are populating the listbox from the displayed range that should directly relate to the ListIndex in the listbox.

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

Function Listbox_ScrollAndSelectItem(ByRef lst As Object, ByVal idx As Long)

    With lst
        .ListIndex = idx - 1
        .TopIndex = idx - 1
    End With
    
End Function

 

[user0]

Why not use the row number of the clicked item to set what's selected in the listbox?

Since you are populating the listbox from the displayed range that should directly relate to the ListIndex in the listbox.

Make sence, but I just copied the code* I use on my real data that has different structure.
* for the sake of information exhaustiveness, e.g. I screw up somewhere.

Please have a look at the video in my previous message.
I have commented list selection code there '''Call Listbox_ScrollAndSelectItem(lstTest, ...), BUT it continues to selects listbox item if listbox appears under cursor.

Simple workaround would be to position userform not in the center of excel window, but next to cursor (left/right or below).
However, I would like to understand why it autoselects listbox item, because it overrides my selection routine Call Listbox_ScrollAndSelectItem.
Is is excel bug or my hands..

 

[Norie]

Is it still showing this behaviour with the code I posted?

Also, in the BeforeDoubleClick event are you cancelling the double click by adding this?

Cancel = True

 

[user0]

Is it still showing this behaviour with the code I posted?

Yes.
I have tested your code - it selects corect item, as long as listbox does not appear under cursor.
If userform's listbox appears under cursor - it automatically selects wrong item.

Also, in the BeforeDoubleClick event are you cancelling the double click by adding this?

Yes, but it does not matter.
This weird behaviour does not affected by Cancel = True, it is still present if it is active or commented.
My click event code:

Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim lRow As Long
    lRow = wsTest.Cells(wsTest.Rows.Count, 1).End(xlUp).Row
   
    If Not Intersect(Target, Range(Cells(1, 1), _
                                   Cells(lRow, 2))) Is Nothing Then
        Cancel = True
        frmTest.Show
    End If
    
End Sub

 

[Dan_W]

Couple of things:
(1) I found it very difficult to recreate your problem. Given where you situate the userform upon initialisation, and given the Intersect requirements in the worksheet_beforedoubleclick event, this is a very unsual problem to have. Your code situates the userform in the middle of the Excel application (wherever it happens to be), but then only activates the code if the doubleclick event occurs in Column A or Column B and on one of the rows containing the tblData data... and the problem only occurs when the mouse pointer happens to be over the listbox on the userform. Like I said... very unusual problem to have.

(2) I think I may have a solution but first I need to know the answer to (admittedly) a very random question - does userform contain an empty event sub for for lstTest_Click? Or is there code in there already?

 

[user0]

(1) I found it very difficult to recreate your problem.

I tried my best describing it )
I have ~150 columns and ~1000 rows in my real file where I use userform with listbox as an "enhanced dropdown" to help populating data as incell dropdown can show only 1 column of data.
It took me some time to realize what causes the problem with item selection.

My current workaround is to open userform next to cursor (eg on the right), but would be awesome know if this "autoselect behaviour" is fixable.

(2) I think I may have a solution but first I need to know the answer to (admittedly) a very random question - does userform contain an empty event sub for for lstTest_Click? Or is there code in there already?

Listbox in the example file has no any event subs, but there are DblClick and KeyDown in real file:

Private Sub lstRealData_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Call update_Value
End Sub

Private Sub lstRealData_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
      Case 13: Call update_Value 'enter
      Case 27: Unload Me         'esc
      Case 70: If Shift = 2 Then txtFilter.SetFocus 'ctrl+f (There is a filter textbox to filter listbox items)
    End Select
End Sub

Private Sub update_Value()
    If lstRealData.ListIndex <> -1 Then
        Application.ActiveCell = lstRealData.Column(0)
        Unload Me
    End If
End Sub

 

[Dan_W]

Well, while I thought I had an answer as to why, after trying it again from scratch today, I've concluded that I have no idea. It seems to me like there is a bug of some sort, which is what prompted my question yesterday. Basically, my original approach was to try and capture the target.value2 in a variable before initialising the Userform, populating the listbox and then selecting the preferred item, thinking that maybe something was going wrong with trying to get at the target.value2 from the Userform (a longshot, I know); there wasn't. But when I was debugging the code by stepping through it line-by-line to track any changes to the variable, 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...).

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). I found that the making it pause with the following works for me:

Sub Pause(period As Single)
    t = Timer
    Do Until Timer - t > period
        DoEvents
    Loop
End Sub

I put it just before calling ScrollAndSelect in the populate_lstTest subroutine, and had to play around with the pause period, but 0.3 seems to work:

    .RowSource = ["tblData"]
    Pause 0.3
    Call Listbox_ScrollAndSelectItem(lstTest, ActiveCell.Row)

It doesn't answer your question, I know, I'm sorry, but I agree that it's odd.