Adding Combo Box Options Automatically and making it dependent

[Jitendra Kumar]

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

 

[Mike]

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, before:=j
NoDupes.Add Swap2, before:=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]

Dear Mike,

Thanks a lot for the solution provided by you. It is working.
I readlly appreciate the way you responded with such a good answer. It is
exactly what was required.

Thanks a lot once again from the bottom of my heart.

 

[Jitendra Kumar]

Dear Mike,

Sorry to bother you again but the code is giving wrong results. I will give
you detailed information so that you can find out the problem.
See the list of Categories is in Sheet2(Code Name of Sheet) and the Range of
Categories start from B1, B1 is the Header and the data continues from B2.
The List of Tasks is in column C starting from C1 which is the Header and the
Task data continues from Cell C2. Can you please advice what is wrong with
the code given by you. I have placed the code in the Userform Initialize
event.
I am awaiting for an early response as it is very urgent.

Thanks a ton in advance..... please I request you to help me get out of this
problem...please