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

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

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

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 -

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 -

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 -