Question Locked

Excel VBA listbox

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Basically what I want is to have a static list inside a list box that when I double click an item it will populate the highlighted cell.

Also if the highlighted cell already has some contents I would like it to just add the new item below on another line.

At the moment I have a user form (userform1) with a list box (listbox1) on it, When I hit ctrl + c the listbox will appear with the list, then when I double click an item it will populate the cell.

But the problem is that when I click another item that I'd like to add into the same cell below the first item it overrides the current contents, instead of adding the item under the first.

The code is very basic and works well all except for the overriding part.

Userform1

Private Sub UserForm_Activate()
ListBox1.AddItem ("item1")
ListBox1.AddItem ("item2")
etc...
End Sub

Listbox1

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim count

ActiveCell.Value = ListBox1.Value

End Sub

I can see exactly why it is overriding the first item so I though putting & vbcrlf at the end of the listbox1.value would work but it still overrides the first item.

I am not very familar with vba but what I think I need is a condition test to see if the cell is empty and if it isn't then add the new item on the line below (easier said then done).

4 replies

by jgarcia102066

ActiveCell.Value = ActiveCell.Value & vbCRLF & ListBox1.Value

If your ActiveCell supports multiple lines then this code should work.

by nimmo

Your a legend buddy that's exactly what I needed.

by paremi

Try using vbLf instead of vbCrLf.

The text should look better like that.
It will look as if you type Alt-Enter. It won't have that little bar that shows the Carriage Return.

This discussion is closed to new replies.