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.
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.
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
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.