Write selections from Excel listbox to cell?

[JPG]

Hi everyone,

I would've thought this was a simple enough question but I've been searching online for an answer all week, including on this site. I've seen answers that seem to be close to what I'm looking for but when I attempt to extrapolate and modify the code I get nothing but error messages. I've also looked through my two Excel 2007 books (each of them 1,000+ pages long) and there is no mention of this subject anywhere.

I am running Excel 2007. I have a multiselect listbox in a worksheet (not a user form). I want a user to be to be able to select multiple items from that listbox and when the the user moves out of the listbox (i.e., when the focus is no longer on the listbox), I want the selections to be copied into a cell on the same worksheet with each of the selections separated by a comma.

So for example, if the listbox (named "ListBox1") contained the following items...

North
East
South
West

...and the user selected both "North" and "West" from the listbox, the following text would get automatically written into cell A2 in the same worksheet after the user move outside of the listbox...

North, West

I know enough VBA to be dangerous (usually just tweaking code written by the macro recorder), but this one is eluding me. My two questions are:
1. What is the VBA code required to give me the result I'm looking for?
2. How would that code be called/where does it reside in the workbook? For example would I put the code in a macro and associate that macro to the listbox object??

Any assistance you can offer me will be appreciated.

Regards,

JohnG

 

[Marcelo Branco]

Hi,

Assuming the name of your ListBox = ListBox1 maybe this

Paste the code below in the worksheet code-page

Private Sub ListBox1_LostFocus()
    Dim listItems As String, i As Long
    
    With ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then listItems = listItems & .List(i) & ", "
        Next i
    End With
    
    Range("A2") = Left(listItems, Len(listItems) - 2)
    
End Sub

 

[Marcelo Branco]

Complementing my previous post

You have to set the MultiSelect property of the ListBox to
1-frmMultiSelectMulti

 

[JPG]

You're a genius! I wasn't using an ActiveX ListBox before, just a standard ListBox. Once I switched to the ActiveX style ListBox, made the necessary adjustments to the name and the properties and applied your code, it worked perfectly.

I've steered clear of ActiveX controls in the past but it looks like I need to learn more about them.

Thank you so much, you have saved me a lot of aggravation!

 

[Marcelo Branco]

You are welcome and tks for the feedback

M.

 

[Marcelo Branco]

oops...

If the user deselects all items an error occurs

Try this new version

Private Sub ListBox1_LostFocus()
    Dim listItems As String, i As Long
    
    With ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then listItems = listItems & .List(i) & ", "
        Next i
    End With
    
    If Len(listItems) > 0 Then
        Range("A2") = Left(listItems, Len(listItems) - 2)
    Else
        Range("A2") = ""
    End If
    
End Sub

M.

 

[JPG]

Thanks very much, I didn't try deselecting all selections but I would've stumbled across the problem eventually. My work PC is shut down for the night, but I'll give the new code a try tomorrow. Thanks again! It's a real relief to have that issue solved.

 

[Adamhumbug]

If you had two list boxes, listbox1 and listbox2, how could you make them both do what the above is designed to do at the same time. I have tried repeating the code but i just get errors.

Thanks

 

[JPG]

If you had two list boxes, listbox1 and listbox2, how could you make them both do what the above is designed to do at the same time. I have tried repeating the code but i just get errors.

Thanks

Unfortunately my memory of this original issue is a little vague since I posted my question a few years ago now. As it turned out I later needed to abandon the Excel approach and instead recreated my form in Access. This turned out to be a better approach for reasons not worth going into here. I quit my job last year and my home computer (a Mac) does not have Office.

I'm hoping somebody else will be able to help you but to get the ball rolling I'll ask a couple of questions others may want to ask you:

Do you want selections from the two list boxes to be written to the same cell (e.g., cell A2) or to two different cells (e.g., listbox1 selections written to cell A2 and listbox2 selections written to cell B2)?

When you say you want the functionality to happen "at the same time" what exactly do you mean and why is the simultaneity of the function necessary? The way I had it working is that the selection(s) would not write to the cell until the mouse focus had moved out of the listbox. Since the user can only be in one listbox at a time, what is the need/benefit for the selections from multiple list boxes writing to the cell(s) at the same time?

Sorry I couldn't be of more help. Good luck!

JPG

 

[Adamhumbug]

Hi, thats for everyones help but this was taking me so much time to do that i have split the "from listbox to sheet" code into two separate buttons. The code i used to get them into a single cell was.

Private Sub CommandButton2_Click()


Sheets("Event_Details").Select
    Dim listItems As String, i As Long
    
    With ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then listItems = listItems & .List(i) & ", "
        Next i
    End With
    
    If Len(listItems) > 0 Then
        Range("B7") = Left(listItems, Len(listItems) - 2)
    Else
        Range("B7") = ""
    End If
    
    Unload Me
    chefForm.Show
    
End Sub