Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I group data in an Excel Combo Box
I am using Excel 2002 and have several combo boxes on a worksheet. Using 'ListFillRange' I have populated the contents of the Combo with data from another worksheet. The problem I have is that the 2 columns of data I am using have many duplicates and I only want my users to see unique values. Is there any way I can do this? I can't delete the duplicates because there are other columns in my range with unique values. Many thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I group data in an Excel Combo Box
You could use ControlFormat.List and pass it a filtered array rather
than fill range. Something along these lines: '--------In a normal code module------------- Public Const myListRange As String = "A1:A100" Sub UpdateMyListBox() Worksheets(1).Shapes(1).ControlFormat.List = _ fcnGetUniqueItems(Range(myListRange)) End Sub Function fcnGetUniqueItems(rng As Range) As Variant Dim c As Range Dim UniqueItems() As String Dim i As Integer 'place the first cell in your range in the array ReDim UniqueItems(0) UniqueItems(0) = rng.Cells(1) For Each c In rng For i = LBound(UniqueItems) To UBound(UniqueItems) If c.Value = UniqueItems(i) And Not c.Value = "" Then Exit For Next i If i UBound(UniqueItems) Then ReDim Preserve UniqueItems(i) UniqueItems(i) = c.Value End If Next c fcnGetUniqueItems = UniqueItems 'You could also sort this array if you preferred End Function '------------------------------------------- Note that this isn't dynamic. So if someone adds something new to the range, the listbox isn't updated. Therefore, if this a problem you should place the following code in the code module of the sheet where your list is to trap any changes and update the listbox accordingly. '------in sheet module where the source range is------------ Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range(myListRange)) _ Is NothingThen UpdateMyListBox End Sub '------------------------ HTH, Gareth wrote: I am using Excel 2002 and have several combo boxes on a worksheet. Using 'ListFillRange' I have populated the contents of the Combo with data from another worksheet. The problem I have is that the 2 columns of data I am using have many duplicates and I only want my users to see unique values. Is there any way I can do this? I can't delete the duplicates because there are other columns in my range with unique values. Many thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a group combo box (Yes/No)? | Excel Discussion (Misc queries) | |||
Group under Data Group and Outline Data not working. Help. | Excel Worksheet Functions | |||
Using a combo box for more than one group of cells | Excel Worksheet Functions | |||
How to clear a group of combo boxes after a submit to another worksheet | Excel Discussion (Misc queries) | |||
Copy Data from One Group of Cells to Another Group | Charts and Charting in Excel |