Nice easy Listbox queries
 

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

Nice easy Listbox queries
 

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)

"Peter Rooney" wrote in message
...
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

Nice easy Listbox queries
 

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


"Bob Phillips" wrote:

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)

"Peter Rooney" wrote in message
...
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

Nice easy Listbox queries
 

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

Nice easy Listbox queries
 

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)

"Peter Rooney" wrote in message
...
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


"Bob Phillips" wrote:

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)

"Peter Rooney" wrote in message
...
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

Nice easy Listbox queries
 

Bob,

That cracked it! Thanks very much!

Have a green tick and a good weekend! :-)

Pete



"Bob Phillips" wrote:

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)

"Peter Rooney" wrote in message
...
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


"Bob Phillips" wrote:

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)

"Peter Rooney" wrote in message
...
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

Nice easy Listbox queries
 

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



"Matt" wrote:

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

Nice easy Listbox queries
 

Thanks, I will.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Peter Rooney" wrote in message
...
Bob,

That cracked it! Thanks very much!

Have a green tick and a good weekend! :-)

Pete



"Bob Phillips" wrote:

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)

"Peter Rooney" wrote in message
...
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


"Bob Phillips" wrote:

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)

"Peter Rooney" wrote in
message
...
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