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

 

[maik_NL]

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

Hi,
I was wondering how you can preselect the listboxes in the above script. Like al listboxes value TRUE as default. Because now you have to select all the listboxes manualy.

thanks in advance.

 

[sdaya]

[h=1]Hi. The above code for "selections from Excel listbox to cell" was very useful for me.Thanks for sharing it here.
As mentioned by one of our friends here please help us by giving a code for this- "If you had two list boxes, listbox1 and listbox2,and texts selected in listbox1 & listbox2 are to be get automatically written into cell A2 in the same worksheet after the user move outside of the two list boxes,what would be the command ? [/h]

 

[stabace]

Thanks so much for the code. It worked perfectly in my userform vba code. I have a tab on my userform with a list of codes on the left and selected codes on the right (KNC_selected). I have an Add, Remove, & Clear button that manages the population of the codes in the KNC_selected list. When the userform "Save" button is pressed, it saves all the userform input fields plus the KNC_selected list to a row in my worksheet. Per the code that you all provided me, it saves the KNC_selected list as input1, input2, input2, etc... into a single cell.

I can now use some help... My userform has the ability to search for a "business name" and then populate all the associated textboxes. How do I repopulate the "KNC_selected" list from the Excel worksheet? As noted from the code below, the retrieved cell may have one or many inputs, separated by commas.

Any help would be greatly appreciated!



This is the tail end of my save routine that works perfectly:
ws.Cells(i, 74) = TotalCapitalization.Value
ws.Cells(i, 75) = TotalEmployees.Value
ws.Cells(i, 76) = KNCcount.Value
'VBA code to populate KNC_selected
Dim listItems As String, r As Long
With KNC_selected
For r = 0 To .ListCount - 1
listItems = listItems & .List(r) & ", "
Next r
End With
If Len(listItems) > 0 Then
ws.Cells(i, 77) = Left(listItems, Len(listItems) - 2)
Else
ws.Cells(i, 77) = ""
End If
End If
Next i
End If
blnNew = False
Call UserFormCSR_Initialize
End Sub

 

[mern1]

Do you have any idea why this code isn't working? (I'm trying to access data from another worksheet and display it in cells on the sheet with the listbox based on the option from the listbox that is selected)

Private Sub ListBox21_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim i As Integer, n As Integer




Set Ws1 = ThisWorkbook.Sheets(2)
n = 1
r = 3
c = 3


For i = 0 To ListBox21.ListCount - 1
If Ws1.Cells(n, 24) = i Then
project = Ws1.Cells(n, 10)
Range("B3").Value = project
End If
Next i


End Sub

Thanks!!