PDA

View Full Version : Solved: 2003 Excel Listbox ? saving multiple selections



Ratbeer
02-07-2008, 07:33 AM
Hello:

I am working with Excel 2003. I have a list box populated with about 1,000 names. I want each successive selection to be assigned to a cell (e.g. first name selected appears in cell C10, second selection in C11...)

I searched the forum and could not find a specific answer to this question. Any help would be very appreciated.

Thank you

:)

mikerickson
02-07-2008, 07:45 AM
Dim i As Long, SelectedCount As Long
SelectedCount = 0
With UserForm1.Listbox1
For i = 0 To .ListCount - 1
If .Selected Then
ThisWorkbook.Sheets("Sheet1").Range("c10").Offset(SelectedCount, 0).Value = .List(i)
SelectedCount = SelectedCount + 1
End If
Next i
End With

Ratbeer
02-08-2008, 09:37 AM
I am getting a run tome error 424 Object required. I am sure this is pretty simple, but I am very new to VBA. Can't attach a file right now. Assume it's a naming problem. The Code breaks at the with Userform line bolded below.

Thank you again

Sub ListBox1_Change()
Dim i As Long, SelectedCount As Long
SelectedCount = 0
With UserForm1.Listbox1
For i = 0 To .ListCount - 1
If .Selected Then
ThisWorkbook.Sheets("Sheet1").Range("c10").Offset(SelectedCount, 0).Value = .List(i)
SelectedCount = SelectedCount + 1
End If
Next i
End With
End Sub

mikerickson
02-08-2008, 01:02 PM
Reviewing this, I caught another error.
Is the List Box named ListBox1 and is it in a userform named Userform1?

Sub ListBox1_Change()
Dim i As Long, SelectedCount As Long
SelectedCount = 0
With UserForm1.Listbox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
ThisWorkbook.Sheets("Sheet1").Range("c10").Offset(SelectedCount, 0).Value = .List(i)
SelectedCount = SelectedCount + 1
End If
Next i
End With
End Sub

Ratbeer
02-09-2008, 01:53 PM
There is no UserForm so I removed that from the code and adjusted sheet and cell references. Still not getting the loop to work and place successive clicks into the below cell and save the previous selections. File attached

Thank you for both your help and patience!

mikerickson
02-10-2008, 11:49 AM
My Mac doesn't support ActiveX controls.

Ratbeer
02-10-2008, 12:33 PM
Thank you again for trying

Bob Phillips
02-10-2008, 04:20 PM
Private Sub ListBox1_Click()
Dim LastRow As Long

With Me

LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
If LastRow = 5 Then LastRow = 6

.Cells(LastRow + 1, "E").Value = ListBox1.Value
End With
End Sub


in the worksheet code module as you have it.

Ratbeer
02-10-2008, 07:24 PM
Excellent, working great

Thank you