ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Fill ComboBox with unique items (https://www.excelbanter.com/excel-programming/384359-fill-combobox-unique-items.html)

Spencer

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

merjet

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




All times are GMT +1. The time now is 04:18 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com