Excel VBA listbox

By Nimmo ·
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.


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


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

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Try this.

by jgarcia102066 In reply to Excel VBA listbox

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

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

Collapse -


by Nimmo In reply to Try this.

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

Collapse -

vbCrLf versus VbLf

by p@re In reply to Perfect

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.

Related Discussions

Related Forums