Solved: 2003 Excel Listbox ? saving multiple selections

[Ratbeer]

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]

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]

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]

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]

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]

My Mac doesn't support ActiveX controls.

[Ratbeer]

Thank you again for trying

[Bob Phillips]

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]

Excellent, working great

Thank you