Adding Combo Box Options Automatically and making it dependent
This may work for you. I found it on somewhere
on the web and tweek it for you
Option Explicit
Private Sub UserForm_Initialize()
Dim rngCatagory As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item, lastRow
' The items are in A1:A to lastrow in column a
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rngCatagory = Range("A1:A" & lastRow)
' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For Each Cell In rngCatagory
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Note: the 2nd argument (key) for the Add method must be a string
Next Cell
' Resume normal error handling
On Error GoTo 0
' Sort the collection (optional)
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, befo=j
NoDupes.Add Swap2, befo=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i
' Add the sorted, non-duplicated items to a ListBox
For Each Item In NoDupes
UserForm1.ComboBox1.AddItem Item
Next Item
End Sub
Private Sub ComboBox1_Change()
Dim rngCatagory As Range, Cell As Range
Dim i As Integer, lastRow
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rngCatagory = Range("A1:A" & lastRow)
ComboBox2.Clear
For Each Cell In rngCatagory
If Cell.Value = ComboBox1.Value Then
ComboBox2.AddItem (Cell.Offset(0, 1).Value)
End If
Next Cell
End Sub
"Jitendra Kumar" wrote:
Dear Excel Experts,
Please help me in getting this done because its very urgently required.. It
will be a great help for me... please
I have two columns of data, Column A contains Category and Column B contains
Task. There are repetitive entries in both the columns. please see the sample
data below:
Category Task
Networking Conduiting - UTP
Networking Laying - UTP
Networking Installation - UTP
Electrical Dedicated Earth
Electrical Ess / Non ess Power
Electrical Conduiting / Laying
What I want to do is, I want to have two combo boxes on a user form with the
names "Category" & "Task" and the "Category" Combo Box should show the
options after extracting the unique entries from column A i.e. Category
column and the "Task" Combo Box should show the Unique entries in Column B
i.e. Task Column. Further more I want that If I select "Electrical" as
Category then the Task Combo Box should show the options available with
"Electrical" Category only and not the options of other Categories like
"Networking".
I hope that I was able to explain what I need and I am seriously looking
forward for a good and useful answer from the experts of this group....
Many Thanks in advance,
Jitendra
|