Listbox Output to Individual Listboxes

  • Thread starter Thread starter Robert D
  • Start date Start date
R

Robert D

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
 
You sure you wanted to post this in an Excel newsgroup?

But I'd try moving the .removeitem into a different loop:

Option Explicit
Private Sub CommandButton1_Click()
Dim employees As Long

'with .multiselect = true, this won't do anything
'well, that's true in excel anyway
'If ListBox1.ListIndex = -1 Then
' Exit Sub
'End If

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)
End If
Next employees

For employees = ListBox1.ListCount - 1 To 0 Step -1
If ListBox1.Selected(employees) = True Then
ListBox1.RemoveItem (employees)
End If
Next employees

End Sub

(Untested, uncompiled. Watch for typos.)
 
Back
Top