Populate unique list in combobox

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


--
Mark

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

"Mark" wrote:

> 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
>
>
> --
> Mark

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

"Jim Thomlinson" wrote:

> 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
>
> "Mark" wrote:
>
> > 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
> >
> >
> > --
> > Mark

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

--
HTH

Bob Phillips

"Thomas" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Jim Thomlinson" wrote:
>
> > 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
> >
> > "Mark" wrote:
> >
> > > 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
> > >
> > >
> > > --
> > > Mark