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

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/

Adding Items to a ListBox-Unique Items Only
 

Worked like a champ! Thanks

--
Message posted from http://www.ExcelForum.com