Nice easy Listbox queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good afternoon, all!

I'm trying to take the selected items from the listbox and add them to the
worksheet range "Target"
However, I'm not quite sure how to reference the value of the selected
item(s) from the listbox.

Any helpful souls out there?

Thanks in advance

Pete



Private Sub UserForm_Initialize()
Range("Target").ClearContents
Dim ListCell As Range
For Each ListCell In Range("CarList")
ListBox1.AddItem ListCell.Value
Next
End Sub

Private Sub cmdOK_Click()
Dim I As Integer
For I = 1 To ListBox1.ListCount
If ListBox1.Selected(I) = True Then
Range("Target").Cells(I, 1).Formula = ListBox1(I).Text
<--------- !
End If
Next I
Unload Me
End Sub
 
Peter,

VBA already does it for you

Listbox1.Value

no need to iterate through the list.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Sorry, Bob, you lost me.
How does this work for multiple selections, then?

Private Sub cmdOK_Click()
Dim I As Integer
For I = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(I) = True Then
Range("Target").Cells(I, 1).Formula = ListBox1.Value
End If
Next I
Unload Me
End Sub

doesn't put anything in my worksheet range at all, either for single or
multiple selections. I use the loop to populate the cells in the target
range, although I admit I could do it another way, using OFFSET.

Sorry to be dense.

Pete
 
Try the following


Private Sub UserForm_Initialize()
Range("Target").ClearContents
Dim ListCell As Range
For Each ListCell In Range("CarList")
ListBox1.AddItem ListCell.Value
Next
End Sub


Private Sub cmdOK_Click()
Dim I As Integer
For I = 1 To ListBox1.ListCount
If ListBox1.Selected(I) = True Then
Range("Target").Cells(I, 1).Formula = ListBox1.List(I)

End If
Next I
Unload Me
End Sub
 
It wouldn't! <g>

Dim I As Integer, j As Integer
For I = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(I) = True Then
j = j + 1
Range("Target").Cells(j, 1).Formula = ListBox1.List(I)
End If
Next I
Unload Me


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Matt,

Thanks for your help. However, I think you need to set up a seperate
variable to determine where in the "Target" range the selected items are to
appear. If you use I for this, the first item selected from the listbox
appears one cell above the first cell of "Target". Bob supplied the
following, incorporating an additional "positioning" variable:

Private Sub cmdOK_Click()
Dim I As Integer, J As Integer
For I = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(I) = True Then
J = J + 1
Range("Target").Cells(I, 1).Formula = ListBox1.List(I)
End If
Next I
Unload Me
End Sub

Thanks again, and have a good weekend! :-)

Pete



Private Sub UserForm_Initialize()
Range("Target").ClearContents
Dim ListCell As Range
For Each ListCell In Range("CarList")
ListBox1.AddItem ListCell.Value
Next
End Sub
 
Thanks, I will.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Back
Top