Mulit selections in a listbox - help me pleaseeee!!!!
Posted by Randycas on July 31, 2001 6:13 AM
I have a worksheet with some headings, i have created a form with these headings which the user will use to update the worksheet. i have a listbox where the user will need to select one or more code(s) from the list. when these are selected they should be listed under one cell in a column called perils.
e.g pb p8 p3 p4 p5
i have been trying to get this done without much progress. Is there any way i can get this done and if it can be done can the codes be placed in the cell with spaces between them?
Posted by Damon Ostrander on July 31, 2001 1:24 PM
Hi Randy,
I'm sure this can be done quite easily. However, you did not mention which cell in the perils column you want the data placed. Is it the selected cell, or perhaps the first available (empty) cell? Also, is the column truly named "perils" (i.e., the range object name) or is this just the column heading? It is simpler if the former.
Damon
Posted by Randycas on August 02, 2001 9:56 AM
Actually, my userform updates the worksheet so every time an entry is made and the user clicks on add record the entry goes to the next available row. perils is the heading at the top of the column.
Posted by Damon Ostrander on August 02, 2001 1:35 PM
Re: Mulit selections in a listbox
Okay, here is some code that I think does what you want. This code would presumably be in your Add Record click event. I just arbitrarily chose column 5 as the perils column, so you would have to put the correct column number in its place.
I hope this is what you wanted. Happy computing.
Damon
Private Sub AddRecordBtn_Click()
Dim SelText As String
Dim i As Integer
Const PerilsCol = 5 'Perils in column E
SelText = ""
With PerilsLbx
For i = 0 To .ListCount - 1
If .Selected(i) Then
SelText = SelText & .List(i) & " "
End If
Next i
End With
' put value in next available cell in perils column
Cells(65536, PerilsCol).End(xlUp).Offset(1).Value = SelText
End Sub
