Adding range search results to multi-column listbox

[Ken Warthen]

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

 

[Per Jessen]

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

 

[Ken Warthen]

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]

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

 

[Ken Warthen]

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]

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

 

[Ken Warthen]

Pers,

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

Ken

 

[Per Jessen]

Ken,

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

Per

 

[Ken Warthen]

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