Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill ComboBox with unique items
I am trying to fill 2 comboboxes with unique items and am not sure where to
begin. they are on a form (frmProducts), cbxCategory and cbxSubCategory. In a worksheet i have something like this: A B Fruits Apple Fruits Orange Vegetables Celery Meats Beef Vegetables Carrots Fruits Grapes Meats Chicken when the form loads, i want the (A) combobox to add only the unique items from column A, and when a category is selected from A, only the appropriate subcategories from B should be added to the subcategory combobox. so if the user chose Fruits from the first one, the second one would list (Apple, Orange and Grapes) Thanks in Advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill ComboBox with unique items
The 2's in the following assume the worksheet has a header row.
Private Sub UserForm_Activate() Dim iEnd As Long Dim aCat() As String Dim i As Long Dim iCt As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") iEnd = ws.Cells(65536, "A").End(xlUp).Row ReDim aCat(1 To iEnd) For i = 2 To iEnd If IsError(Application.Match(ws.Cells(i, "A"), aCat, 0)) Then iCt = iCt + 1 aCat(iCt) = ws.Cells(i, "A") End If Next i ReDim Preserve aCat(1 To iCt) cbxCategory.List = aCat End Sub Private Sub cbxCategory_Change() Dim iEnd As Long Dim c As Range Dim rng As Range Dim ws As Worksheet cbxSubcategory.Clear Set ws = Worksheets("Sheet1") iEnd = ws.Cells(65536, "A").End(xlUp).Row Set rng = ws.Range("A2:A" & iEnd) For Each c In rng If c = cbxCategory Then _ cbxSubcategory.AddItem c.Offset(0, 1) Next c End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fill combobox depending on selection from another combobox | Excel Discussion (Misc queries) | |||
Combobox items determined by the selection in another combobox | Excel Programming | |||
Adding Items to a ListBox-Unique Items Only | Excel Programming | |||
ComboBox Items | Excel Programming | |||
ComboBox items | Excel Programming |