VBA ListBox .AddItem - Line Spacing

[sthrncaliguy]

I have a quick question..

I am populating a listbox via .additem, but when I do this there are several blank lines inserted between each item. I am not sure why this is the case.

I am adding items via a combobox..

So when the user changes a combobox, the onchange code reads:

With myListBox
.additem myCboBox.Value
end with
myCboBox.Value = ""

I am not sure why there is such a gap between each added item, but would like to know if there is a way around this... I didn't see any formatting options in the listbox properties that looked like they related to this behavior..

 

[Leith Ross]

Hello ,

The problem is the ComboBox event you are using. The Change event, which is the default event, is triggered a second time by the statement:

    myCboBox.Value = ""

This adds in the blank line. To eliminate this, you need to move your code to the ComboBox_Click event procedure. Copy this code and paste it into your UserForm and then delete the old Change event code.

Private Sub CboBox_Click()
    With myListBox
      .AddItem myCboBox.Value
    End With
    myCboBox.Value = ""
End Sub

Sincerely,
Leith Ross