Listbox Output to Individual Listboxes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robert D - 07 Feb 2008 23:55 GMT

I'm struggling to design a simple report in Word with visible CommandButtons
that will allow the user to open a UserForm, select appropriate data from
ListBoxes, and upon exiting the UserForm, populate individual ListBoxes on
the Word report. The respective UserForm1 consists of two ListBoxes (In
brief, ListBox1 consists of the available data - i.e., employee names;
ListBox2 will populate with the selected data via CommandButton1). I would
like the selected items in ListBox1 to not only populate in ListBox2 on the
UserForm1 (which I’ve accomplished), but also populate ListBoxes in my Word
Report (a single item per ListBox). The code I currently have populates all
ListBoxes on my Word report with all selected items. I’d appreciate any and
all advise/direction. (below you’ll find the most relevant code) Thank you.

Dim employees As Integer
Private Sub CommandButton1_Click()
If ListBox1.ListIndex = -1 Then Exit Sub
For employees = ListBox1.ListCount - 1 To 0 Step -1
If ListBox1.Selected(employees) = True Then
ListBox2.AddItem ListBox1.List(employees)
Report.ListBox1.AddItem ListBox1.List(employees)
Report.ListBox2.AddItem ListBox1.List(employees)
Report.ListBox3.AddItem ListBox1.List(employees)
Report.ListBox4.AddItem ListBox1.List(employees)
Report.ListBox5.AddItem ListBox1.List(employees)
Report.ListBox6.AddItem ListBox1.List(employees)
Report.ListBox7.AddItem ListBox1.List(employees)
Report.ListBox8.AddItem ListBox1.List(employees)
Report.ListBox9.AddItem ListBox1.List(employees)
Report.ListBox10.AddItem ListBox1.List(employees)
ListBox1.RemoveItem (employees)
End If
Next employees
End Sub

Reply to this Message

Tony Strazzeri - 14 Feb 2008 00:12 GMT

Hi Robert,

The code below (essentially your code) works for me.

Note: Every time this is run it will add the selected item to the
listboxes on the document, whereas the second listbox on the form will
only ever show the selected item. This happens because the loading of
the form explicitly clears the second listbox whereas the listboxes on
the document are not explicitly cleared.

Hope this helps.

Cheers
TonyS.

Dim Report As Document

Private Sub CommandButton1_Click()
Dim employees As Integer
If ListBox1.ListIndex = -1 Then Exit Sub
For employees = ListBox1.ListCount - 1 To 0 Step -1
If ListBox1.Selected(employees) = True Then
ListBox2.AddItem ListBox1.List(employees)
Report.ListBox1.AddItem ListBox1.List(employees)
Report.ListBox2.AddItem ListBox1.List(employees)
Report.ListBox3.AddItem ListBox1.List(employees)
Report.ListBox4.AddItem ListBox1.List(employees)
Report.ListBox5.AddItem ListBox1.List(employees)
ListBox1.RemoveItem (employees)
End If
Next employees
End Sub

Private Sub UserForm_Initialize()
With ListBox1
For I = 1 To 5
.AddItem "lb1..." & I
Next
End With
Set Report = ActiveDocument

End Sub

Reply to this Message