Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a VBA listbox to populate a worksheet
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).Ro w + 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a VBA listbox to populate a worksheet
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) "Paulc" wrote in message ... 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).Ro w + 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populate Listbox with File Name Only | Excel Programming | |||
Using Hidden Sheets to populate listbox | Excel Programming | |||
populate listbox | Excel Programming | |||
Populate listBox | Excel Programming | |||
ListBox Populate | Excel Programming |