Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Items to a ListBox-Unique Items Only
Yes, another ListBox issue, but of a different sort.
I would like to add only unique items to the listbox. the list I a working with for example has a row PER "wholesaler" for each "Item that is carried. A "wholesaler" may carry multiple items. I want t fill the listbox with the "wholesaler" name only once (not once fo each item). the code I have is below. It currently adds the wholesaler each tim the condition is met (not just once). Thanks in advance for any help. Sub FilterWholesalers() Dim Row As Integer Set testWholesaler = ThisWorkbook.Sheets("lookup on brewery no refrig") TopicCount Application.WorksheetFunction.CountA(testWholesale r.Range("A:A")) cbWholesaler.Clear For Row = 1 To TopicCount If Sheet4.Cells(Row, 4) = cbState.Value Then cbWholesaler.AddItem testWholesaler.Cells(Row, 3).Value End If Next Row cbWholesaler.ListIndex = 0 CurrentTopic = 1 End Su -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Items to a ListBox-Unique Items Only
There are other ways, but using your current code:
Sub FilterWholesalers() Dim Row As Integer, i as Long, j as Long Dim bUnique as Boolean Set testWholesaler = ThisWorkbook.Sheets("lookup on brewery non refrig") TopicCount = Application.WorksheetFunction.CountA(testWholesale r.Range("A:A")) cbWholesaler.Clear For Row = 1 To TopicCount If Sheet4.Cells(Row, 4) = cbState.Value Then if cbWholesaler.Listcount = 0 then cbWholesaler.AddItem testWholesaler.Cells(Row, 3).Value else bUnique = True for j = 0 to cbWholesaler.Listcount -1 if cbWholesaler.List(j) = testWholesaler.Cells(row,3).Value then bUnique = False exit for end if Next if bUnique then cbWholesaler.AddItem testWholesaler.Cells(row,3).Value end if End If Next Row cbWholesaler.ListIndex = 0 CurrentTopic = 1 End Sub code is untested and may have problems, but represents an approach. -- Regards, Tom Ogilvy "jpendegraft " wrote in message ... Yes, another ListBox issue, but of a different sort. I would like to add only unique items to the listbox. the list I am working with for example has a row PER "wholesaler" for each "Item" that is carried. A "wholesaler" may carry multiple items. I want to fill the listbox with the "wholesaler" name only once (not once for each item). the code I have is below. It currently adds the wholesaler each time the condition is met (not just once). Thanks in advance for any help. Sub FilterWholesalers() Dim Row As Integer Set testWholesaler = ThisWorkbook.Sheets("lookup on brewery non refrig") TopicCount = Application.WorksheetFunction.CountA(testWholesale r.Range("A:A")) cbWholesaler.Clear For Row = 1 To TopicCount If Sheet4.Cells(Row, 4) = cbState.Value Then cbWholesaler.AddItem testWholesaler.Cells(Row, 3).Value End If Next Row cbWholesaler.ListIndex = 0 CurrentTopic = 1 End Sub --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Items to a ListBox-Unique Items Only
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding items to columns in a listbox | Excel Discussion (Misc queries) | |||
Setting up a validation of data listbox to provide the unique items within a range | Excel Worksheet Functions | |||
Adding Items to ListBox-eliminating Blanks | Excel Programming | |||
Adding Items to a LISTBOX--eliminating Blank Lines | Excel Programming | |||
Adding items to a spreadsheet from a user form listbox | Excel Programming |