Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate unique list in combobox
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate unique list in combobox
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate unique list in combobox
cell is getting the next single cell object within the range, so it has a
value property. -- HTH Bob Phillips "Thomas" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Unique Values in as a combobox value list | Excel Discussion (Misc queries) | |||
How do i populate a text box according to selection in combobox? | Excel Worksheet Functions | |||
Populate combobox | Excel Programming | |||
populate combobox with sheet names | Excel Programming | |||
Populate a combobox | Excel Programming |