Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Combo Box Options Automatically and making it dependent
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Combo Box Options Automatically and making it dependent
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. :-) -- Best Regards, Jitendra Kumar "Mike" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Combo Box Options Automatically and making it dependent
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 -- Best Regards, Jitendra Kumar "Jitendra Kumar" wrote: 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. :-) -- Best Regards, Jitendra Kumar "Mike" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop down list options dependent on cell value | Excel Discussion (Misc queries) | |||
Dependent (linked) combo boxes | Excel Discussion (Misc queries) | |||
Comparing relative cells and making values dependent | Excel Programming | |||
Excel VBA Combo Box Populating dependent on other combo box choices | Excel Programming | |||
Dependent list in Combo-Box | Excel Programming |