Excel VBA Userform Initialize populates Listbox but shows random selected items

Question

I'm using Excel 2013. I have a "BeforeDoubleClick" event that shows a userform with some listboxes. The user double clicks any cell and the userform intializes and populates the listboxes, however when the userform is launched the listbox shows a couple of the items as highlighted (already selected, even though I haven't selected anything yet):

This is the code to load the listbox when userform is initialized:

For Each cel In rng.Cells
  With cel
  If .Value <> "" Then
    lstPEOPLE.AddItem .Value
  End If
  End With
Next cel

Obviously the listbox should be clear of any selections on initialization. This doesn't happen all the time - it seems to happen when randomly double-clicking cells. Thoughts?

Thank you.

have you thought of using a ComboBox that references a Table with the Names instead of a range of cells? Then you could just use the .RowSource on the BeforeDoubleClick event. — Zack E
– Zack E, Commented Dec 21, 2018 at 18:09

Zack E · Accepted Answer · 2018-12-21 19:30:54Z

1

If you set all of the names in that list to a table you could just use the following code in the BeforeDoubleClick event:

lstPeople.RowSource="Insert Table Name Here"

answered Dec 21, 2018 at 19:30

Zack E

7182 gold badges7 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zack E Over a year ago

Glad I could help.

Collectives™ on Stack Overflow

Excel VBA Userform Initialize populates Listbox but shows random selected items

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related