Adding range search results to multi-column listbox
 

I use the following code to search through a range for store numbers given a
group number.

For Each Cell In Range("StoreList")
If Cell.Value = strGroupName Then
strStore = Cell.Offset(0, -3).Value
Me.lstStores.AddItem (strStore)
End If
Next Cell

Instead of just showing the store number (strStore) in the listbox, I'd like
to have a two column listbox displaying both store number and group name
(strGroupName). I'm assuming this would require building an array and using
the elements of the array as the record source for the multicolumn listbox,
but I'm not sure, and arrays don't work well in my brain. Any help would be
appreciated.

Ken

Adding range search results to multi-column listbox
 

Hi Ken

You don't need an array. It can be done like this. Just remember to
change ColumnCount in Listbox properties to 2 :

For Each Cell In Range("StoreList")
If Cell.Value = strGroupName Then
strStore = Cell.Offset(0, -3).Value
With Me.lstStores
.AddItem (strStore)
.List(.ListCount - 1, 1) = strGroupName
End With
End If
Next Cell

Regards,
Per

On 21 Apr., 20:14, Ken Warthen
wrote:
I use the following code to search through a range for store numbers given a
group number.

* * * * For Each Cell In Range("StoreList")
* * * * * * If Cell.Value = strGroupName Then
* * * * * * * * strStore = Cell.Offset(0, -3).Value
* * * * * * * * Me.lstStores.AddItem (strStore)
* * * * * * End If
* * * * Next Cell

Instead of just showing the store number (strStore) in the listbox, I'd like
to have a two column listbox displaying both store number and group name
(strGroupName). *I'm assuming this would require building an array and using
the elements of the array as the record source for the multicolumn listbox,
but I'm not sure, and arrays don't work well in my brain. *Any help would be
appreciated.

Ken

Adding range search results to multi-column listbox
 

Per,

Thanks for the code. That did the trick. Would it be too much to ask how I
would reference a selected item in the listbox in such a way that I could add
the selected item (both columns) to a second listbox (lstSelectedStores).
This would be code in the OnClick event of a command button. Thanks again
for the help.

Ken

"Per Jessen" wrote:

Hi Ken

You don't need an array. It can be done like this. Just remember to
change ColumnCount in Listbox properties to 2 :

For Each Cell In Range("StoreList")
If Cell.Value = strGroupName Then
strStore = Cell.Offset(0, -3).Value
With Me.lstStores
.AddItem (strStore)
.List(.ListCount - 1, 1) = strGroupName
End With
End If
Next Cell

Regards,
Per

On 21 Apr., 20:14, Ken Warthen
wrote:
I use the following code to search through a range for store numbers given a
group number.

For Each Cell In Range("StoreList")
If Cell.Value = strGroupName Then
strStore = Cell.Offset(0, -3).Value
Me.lstStores.AddItem (strStore)
End If
Next Cell

Instead of just showing the store number (strStore) in the listbox, I'd like
to have a two column listbox displaying both store number and group name
(strGroupName). I'm assuming this would require building an array and using
the elements of the array as the record source for the multicolumn listbox,
but I'm not sure, and arrays don't work well in my brain. Any help would be
appreciated.

Ken

Adding range search results to multi-column listbox
 

Ken,

With both listboxes on same userform this should do it:

Private Sub CommandButton1_Click()
SelItem = Me.lstStores.ListIndex
With Me.lstSelectedStores
.AddItem Me.lstStores.List(SelItem, 0)
.List(.ListCount - 1, 1) = Me.lstStores.List(SelItem, 1)
End With
End Sub


Best regards,
Per

On 22 Apr., 01:16, Ken Warthen
wrote:
Per,

Thanks for the code. *That did the trick. *Would it be too much to ask how I
would reference a selected item in the listbox in such a way that I could add
the selected item (both columns) to a second listbox (lstSelectedStores). *
This would be code in the OnClick event of a command button. *Thanks again
for the help.

Ken



"Per Jessen" wrote:
Hi Ken

You don't need an array. It can be done like this. Just remember to
change ColumnCount in Listbox properties to 2 :

For Each Cell In Range("StoreList")
* * If Cell.Value = strGroupName Then
* * * * strStore = Cell.Offset(0, -3).Value
* * * * With Me.lstStores
* * * * * * .AddItem (strStore)
* * * * * * .List(.ListCount - 1, 1) = strGroupName
* * * * End With
* * End If
Next Cell

Regards,
Per

On 21 Apr., 20:14, Ken Warthen
wrote:
I use the following code to search through a range for store numbers given a
group number.

* * * * For Each Cell In Range("StoreList")
* * * * * * If Cell.Value = strGroupName Then
* * * * * * * * strStore = Cell.Offset(0, -3).Value
* * * * * * * * Me.lstStores.AddItem (strStore)
* * * * * * End If
* * * * Next Cell

Instead of just showing the store number (strStore) in the listbox, I'd like
to have a two column listbox displaying both store number and group name
(strGroupName). *I'm assuming this would require building an array and using
the elements of the array as the record source for the multicolumn listbox,
but I'm not sure, and arrays don't work well in my brain. *Any help would be
appreciated.

Ken- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Adding range search results to multi-column listbox
 

Per,

Actually your code adds the same record repeatedly. If I have six records
selected in lstStores, clicking the add button adds six of the first selected
record to lstSelectedStores.

Ken

"Per Jessen" wrote:

Ken,

With both listboxes on same userform this should do it:

Private Sub CommandButton1_Click()
SelItem = Me.lstStores.ListIndex
With Me.lstSelectedStores
.AddItem Me.lstStores.List(SelItem, 0)
.List(.ListCount - 1, 1) = Me.lstStores.List(SelItem, 1)
End With
End Sub


Best regards,
Per

On 22 Apr., 01:16, Ken Warthen
wrote:
Per,

Thanks for the code. That did the trick. Would it be too much to ask how I
would reference a selected item in the listbox in such a way that I could add
the selected item (both columns) to a second listbox (lstSelectedStores).
This would be code in the OnClick event of a command button. Thanks again
for the help.

Ken



"Per Jessen" wrote:
Hi Ken

You don't need an array. It can be done like this. Just remember to
change ColumnCount in Listbox properties to 2 :

For Each Cell In Range("StoreList")
If Cell.Value = strGroupName Then
strStore = Cell.Offset(0, -3).Value
With Me.lstStores
.AddItem (strStore)
.List(.ListCount - 1, 1) = strGroupName
End With
End If
Next Cell

Regards,
Per

On 21 Apr., 20:14, Ken Warthen
wrote:
I use the following code to search through a range for store numbers given a
group number.

For Each Cell In Range("StoreList")
If Cell.Value = strGroupName Then
strStore = Cell.Offset(0, -3).Value
Me.lstStores.AddItem (strStore)
End If
Next Cell

Instead of just showing the store number (strStore) in the listbox, I'd like
to have a two column listbox displaying both store number and group name
(strGroupName). I'm assuming this would require building an array and using
the elements of the array as the record source for the multicolumn listbox,
but I'm not sure, and arrays don't work well in my brain. Any help would be
appreciated.

Ken- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Adding range search results to multi-column listbox
 

Ken

You never told that you have enabled MultiSelect.

Private Sub CommandButton1_Click()
For lItem = 0 To Me.lstStores.ListCount - 1
If Me.lstStores.Selected(lItem) = True Then
With Me.lstSelectedStores
.AddItem Me.lstStores.List(lItem, 0)
.List(.ListCount - 1, 1) = Me.lstStores.List(lItem, 1)
End With
End If
Next
End Sub

Per

On 22 Apr., 01:57, Ken Warthen
wrote:
Per,

Actually your code adds the same record repeatedly. *If I have six records
selected in lstStores, clicking the add button adds six of the first selected
record to lstSelectedStores.

Ken



"Per Jessen" wrote:
Ken,

With both listboxes on same userform this should do it:

Private Sub CommandButton1_Click()
SelItem = Me.lstStores.ListIndex
With Me.lstSelectedStores
* * .AddItem Me.lstStores.List(SelItem, 0)
* * .List(.ListCount - 1, 1) = Me.lstStores.List(SelItem, 1)
End With
End Sub

Best regards,
Per

On 22 Apr., 01:16, Ken Warthen
wrote:
Per,

Thanks for the code. *That did the trick. *Would it be too much to ask how I
would reference a selected item in the listbox in such a way that I could add
the selected item (both columns) to a second listbox (lstSelectedStores). *
This would be code in the OnClick event of a command button. *Thanks again
for the help.

Ken

"Per Jessen" wrote:
Hi Ken

You don't need an array. It can be done like this. Just remember to
change ColumnCount in Listbox properties to 2 :

For Each Cell In Range("StoreList")
* * If Cell.Value = strGroupName Then
* * * * strStore = Cell.Offset(0, -3).Value
* * * * With Me.lstStores
* * * * * * .AddItem (strStore)
* * * * * * .List(.ListCount - 1, 1) = strGroupName
* * * * End With
* * End If
Next Cell

Regards,
Per

On 21 Apr., 20:14, Ken Warthen
wrote:
I use the following code to search through a range for store numbers given a
group number.

* * * * For Each Cell In Range("StoreList")
* * * * * * If Cell.Value = strGroupName Then
* * * * * * * * strStore = Cell.Offset(0, -3).Value
* * * * * * * * Me.lstStores.AddItem (strStore)
* * * * * * End If
* * * * Next Cell

Instead of just showing the store number (strStore) in the listbox, I'd like
to have a two column listbox displaying both store number and group name
(strGroupName). *I'm assuming this would require building an array and using
the elements of the array as the record source for the multicolumn listbox,
but I'm not sure, and arrays don't work well in my brain. *Any help would be
appreciated.

Ken- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Adding range search results to multi-column listbox
 

Pers,

Sorry for the omission. Thanks a million for the help. Out of curiosity,
where are you writing from?

Ken

"Per Jessen" wrote:

Ken

You never told that you have enabled MultiSelect.

Private Sub CommandButton1_Click()
For lItem = 0 To Me.lstStores.ListCount - 1
If Me.lstStores.Selected(lItem) = True Then
With Me.lstSelectedStores
.AddItem Me.lstStores.List(lItem, 0)
.List(.ListCount - 1, 1) = Me.lstStores.List(lItem, 1)
End With
End If
Next
End Sub

Per

On 22 Apr., 01:57, Ken Warthen
wrote:
Per,

Actually your code adds the same record repeatedly. If I have six records
selected in lstStores, clicking the add button adds six of the first selected
record to lstSelectedStores.

Ken



"Per Jessen" wrote:
Ken,

With both listboxes on same userform this should do it:

Private Sub CommandButton1_Click()
SelItem = Me.lstStores.ListIndex
With Me.lstSelectedStores
.AddItem Me.lstStores.List(SelItem, 0)
.List(.ListCount - 1, 1) = Me.lstStores.List(SelItem, 1)
End With
End Sub

Best regards,
Per

On 22 Apr., 01:16, Ken Warthen
wrote:
Per,

Thanks for the code. That did the trick. Would it be too much to ask how I
would reference a selected item in the listbox in such a way that I could add
the selected item (both columns) to a second listbox (lstSelectedStores).
This would be code in the OnClick event of a command button. Thanks again
for the help.

Ken

"Per Jessen" wrote:
Hi Ken

You don't need an array. It can be done like this. Just remember to
change ColumnCount in Listbox properties to 2 :

For Each Cell In Range("StoreList")
If Cell.Value = strGroupName Then
strStore = Cell.Offset(0, -3).Value
With Me.lstStores
.AddItem (strStore)
.List(.ListCount - 1, 1) = strGroupName
End With
End If
Next Cell

Regards,
Per

On 21 Apr., 20:14, Ken Warthen
wrote:
I use the following code to search through a range for store numbers given a
group number.

For Each Cell In Range("StoreList")
If Cell.Value = strGroupName Then
strStore = Cell.Offset(0, -3).Value
Me.lstStores.AddItem (strStore)
End If
Next Cell

Instead of just showing the store number (strStore) in the listbox, I'd like
to have a two column listbox displaying both store number and group name
(strGroupName). I'm assuming this would require building an array and using
the elements of the array as the record source for the multicolumn listbox,
but I'm not sure, and arrays don't work well in my brain. Any help would be
appreciated.

Ken- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Adding range search results to multi-column listbox
 

Ken,

Thanks for your reply. I'm writing from Copenhangen, Denmark.

Per

On 22 Apr., 02:42, Ken Warthen
wrote:
Pers,

Sorry for the omission. *Thanks a million for the help. *Out of curiosity,
where are you writing from?

Ken



"Per Jessen" wrote:
Ken

You never told that you have enabled MultiSelect.

Private Sub CommandButton1_Click()
For lItem = 0 To Me.lstStores.ListCount - 1
* * If Me.lstStores.Selected(lItem) = True Then
* * * * With Me.lstSelectedStores
* * * * * * .AddItem Me.lstStores.List(lItem, 0)
* * * * * * .List(.ListCount - 1, 1) = Me.lstStores.List(lItem, 1)
* * * * End With
* * End If
Next
End Sub

Per

On 22 Apr., 01:57, Ken Warthen
wrote:
Per,

Actually your code adds the same record repeatedly. *If I have six records
selected in lstStores, clicking the add button adds six of the first selected
record to lstSelectedStores.

Ken

"Per Jessen" wrote:
Ken,

With both listboxes on same userform this should do it:

Private Sub CommandButton1_Click()
SelItem = Me.lstStores.ListIndex
With Me.lstSelectedStores
* * .AddItem Me.lstStores.List(SelItem, 0)
* * .List(.ListCount - 1, 1) = Me.lstStores.List(SelItem, 1)
End With
End Sub

Best regards,
Per

On 22 Apr., 01:16, Ken Warthen
wrote:
Per,

Thanks for the code. *That did the trick. *Would it be too much to ask how I
would reference a selected item in the listbox in such a way that I could add
the selected item (both columns) to a second listbox (lstSelectedStores). *
This would be code in the OnClick event of a command button. *Thanks again
for the help.

Ken

"Per Jessen" wrote:
Hi Ken

You don't need an array. It can be done like this. Just remember to
change ColumnCount in Listbox properties to 2 :

For Each Cell In Range("StoreList")
* * If Cell.Value = strGroupName Then
* * * * strStore = Cell.Offset(0, -3).Value
* * * * With Me.lstStores
* * * * * * .AddItem (strStore)
* * * * * * .List(.ListCount - 1, 1) = strGroupName
* * * * End With
* * End If
Next Cell

Regards,
Per

On 21 Apr., 20:14, Ken Warthen
wrote:
I use the following code to search through a range for store numbers given a
group number.

* * * * For Each Cell In Range("StoreList")
* * * * * * If Cell.Value = strGroupName Then
* * * * * * * * strStore = Cell.Offset(0, -3).Value
* * * * * * * * Me.lstStores.AddItem (strStore)
* * * * * * End If
* * * * Next Cell

Instead of just showing the store number (strStore) in the listbox, I'd like
to have a two column listbox displaying both store number and group name
(strGroupName). *I'm assuming this would require building an array and using
the elements of the array as the record source for the multicolumn listbox,
but I'm not sure, and arrays don't work well in my brain. *Any help would be
appreciated.

Ken- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Adding range search results to multi-column listbox
 

Pers,

My grandfather, Anton Jensen, was from Copenhagen which makes me half Dane.
He moved to the U.S. sometime in the early 20th century. He worked as a
dairy farmer in Nebraska most of his life, as did a couple of his brothers.
Unfortunately, I don't know a lot about Denmark, except the popular media
stereotypes, of tall, beautiful, blonde women, and a liberal cultural, both
of which seem pretty attractive to me.

Ken

"Per Jessen" wrote:

Ken,

Thanks for your reply. I'm writing from Copenhangen, Denmark.

Per

On 22 Apr., 02:42, Ken Warthen
wrote:
Pers,

Sorry for the omission. Thanks a million for the help. Out of curiosity,
where are you writing from?

Ken



"Per Jessen" wrote:
Ken

You never told that you have enabled MultiSelect.

Private Sub CommandButton1_Click()
For lItem = 0 To Me.lstStores.ListCount - 1
If Me.lstStores.Selected(lItem) = True Then
With Me.lstSelectedStores
.AddItem Me.lstStores.List(lItem, 0)
.List(.ListCount - 1, 1) = Me.lstStores.List(lItem, 1)
End With
End If
Next
End Sub

Per

On 22 Apr., 01:57, Ken Warthen
wrote:
Per,

Actually your code adds the same record repeatedly. If I have six records
selected in lstStores, clicking the add button adds six of the first selected
record to lstSelectedStores.

Ken

"Per Jessen" wrote:
Ken,

With both listboxes on same userform this should do it:

Private Sub CommandButton1_Click()
SelItem = Me.lstStores.ListIndex
With Me.lstSelectedStores
.AddItem Me.lstStores.List(SelItem, 0)
.List(.ListCount - 1, 1) = Me.lstStores.List(SelItem, 1)
End With
End Sub

Best regards,
Per

On 22 Apr., 01:16, Ken Warthen
wrote:
Per,

Thanks for the code. That did the trick. Would it be too much to ask how I
would reference a selected item in the listbox in such a way that I could add
the selected item (both columns) to a second listbox (lstSelectedStores).
This would be code in the OnClick event of a command button. Thanks again
for the help.

Ken

"Per Jessen" wrote:
Hi Ken

You don't need an array. It can be done like this. Just remember to
change ColumnCount in Listbox properties to 2 :

For Each Cell In Range("StoreList")
If Cell.Value = strGroupName Then
strStore = Cell.Offset(0, -3).Value
With Me.lstStores
.AddItem (strStore)
.List(.ListCount - 1, 1) = strGroupName
End With
End If
Next Cell

Regards,
Per

On 21 Apr., 20:14, Ken Warthen
wrote:
I use the following code to search through a range for store numbers given a
group number.

For Each Cell In Range("StoreList")
If Cell.Value = strGroupName Then
strStore = Cell.Offset(0, -3).Value
Me.lstStores.AddItem (strStore)
End If
Next Cell

Instead of just showing the store number (strStore) in the listbox, I'd like
to have a two column listbox displaying both store number and group name
(strGroupName). I'm assuming this would require building an array and using
the elements of the array as the record source for the multicolumn listbox,
but I'm not sure, and arrays don't work well in my brain. Any help would be
appreciated.

Ken- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -