Listbox Output to Individual Listboxes

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
 
D

Dave Peterson

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.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top