Listbox autoselects item if openned under cursor via cell doubleclick

user0

New Member
Joined
Aug 2, 2020
Messages
6
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
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.

VBA Code:
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.
 

Attachments

  • test_1.png
    test_1.png
    55.8 KB · Views: 12

user0

New Member
Joined
Aug 2, 2020
Messages
6
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
..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!
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
378
Office Version
  1. 365
Platform
  1. Windows
Turns out its not just you who has experienced this problem!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,829
Messages
5,627,143
Members
416,223
Latest member
RichardHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top