Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search Results to Listbox | Excel Programming | |||
Search Results to Listbox | Excel Programming | |||
VBA code to search and display results in multicolumn listbox | Excel Programming | |||
Multi Column Listbox Help | Excel Programming | |||
AddItem with multi-column listBox | Excel Programming |