Populate unique list in combobox

[Guest]

I am using Excel 97, can someone please help me with some code.

I want to populate a unique list in a combobox on a userform from the rows
in column b2 until the end which contain data.

Then when I select one of the entries in the combobox the autofilter is
displayed with all the rows with the value of the combobox.

Many thanks


Mark

 

[Guest]

Here is some code to get the uniqu items into your combobox. You need to
reference your project to "Microsoft Scripting Runtime" for this code to work.

Private Sub GetUniqueItems()
Dim cell As Range 'Current cell in range to check
Dim rngToSearch As Range 'Cells to be searched
Dim dic As Scripting.Dictionary 'Dictionary Object
Dim dicItem As Variant 'Items within dictionary object

'Create range to be searched
Set rngToSearch = Intersect(ActiveSheet.UsedRange,
ActiveSheet.Range("B2:B65000"))

'Confirm there is a relevant range selected
If Not rngToSearch Is Nothing Then
'Create dictionay object
Set dic = New Scripting.Dictionary

'Populate dictionary object with unique items (use key to define
unique)
For Each cell In rngToSearch 'Traverse selected range
If Not dic.Exists(cell.Value) Then 'Check the key
dic.Add cell.Value, cell.Value 'Add the item if unique
End If
Next

If Not dic Is Nothing Then 'Check for dictionary
For Each dicItem In dic.Items 'Loop through dictionary
'***Add to ComboBox Here*** cbxMyBox.Add dicItem
Next dicItem
'Clean up objects
Set dic = Nothing
End If
End If

End Sub

Once you get this working then all that is left is the filter. Record a
macro for that and you should be able to figure out the rest.

HTH

 

[Guest]

I have 1 question concerning your source code.
What do the Variable "cell" do because it dont get a Value.
Because of that, this source code doesnt run on my Excel-Macro.

Thanks a lot.

Thomas

 

[Bob Phillips]

cell is getting the next single cell object within the range, so it has a
value property.