Using a VBA listbox to populate a worksheet

[Guest]

Hi, using XL 2000

I am populating two listboxes in Userform1 using..

With Materials 'listbox1
i = 2
Do While i < Worksheets("Content").Range("D65536").End(xlUp).Row + 1
.AddItem Worksheets("Content").Cells(i, "D")
ListBox2.AddItem Worksheets("Content").Cells(i, "D")
i = i + 1
Loop
End With

When the user selects items in listbox2, I need the selected choices to
populate a defined row in sheet1, preferably dynamically, cell by cell. In
other words if 5 choices were checked in Listbox2, I need each choice placed
in each consecutive cells in sheet1, as they were being checked - A1...A5.

I also need to remove a choice from the respective row in sheet1 by
unchecking the choice in the same listbox2.

Hope i've explained sufficiently. Any ideas appreciated.

Thank you, Paul

 

[Bob Phillips]

Paul,

Here is some code. I think there are bits missing as you haven't covered
all situations in your description

Private Sub CommandButton1_Click()
Dim i As Long
Dim j As Long

With Me.Materials
For i = 0 To .ListCount - 1
If .Selected(i) Then
j = j + 1
Cells(j, "A").Value = .List(i)
End If
Next i
End With

End Sub

Private Sub ListBox2_Click()
Dim i As Long

On Error Resume Next
i = WorksheetFunction.Match(Me.ListBox2.Value, ActiveSheet.Range("A:A"),
0)
On Error GoTo 0
If i > 0 Then
ActiveSheet.Cells(i, "A").Value = ""
End If

End Sub

Private Sub UserForm_Initialize()
Dim i As Long
Dim cLastRow As Long

With Worksheets("Content")
cLastRow = .Cells(Rows.Count, "D").End(xlUp).row + 1
For i = 2 To cLastRow
Materials.AddItem .Cells(i, "D")
ListBox2.AddItem Worksheets("Content").Cells(i, "D")
Next i
End With

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)