Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Combobox problem .
Hi, I have a problem in excel.. I have ComboBox1 which contains A, B, C, D. and ComboBox2 which I want to dynamically changed based on the choice made in ComboBox1. ComboBox1 is already a filtered selection of column A A1 A2 A3 A Catagory 1 Release a A Catagory 2 Release b A Catagory 3 Release c B Catagory 1 Release a B Catagory 2 Release b C Catagory 1 Release a D Catagory 1 Release a So when I choose A in ComboBox1 I want combobox2 to show a concatination of A2 and A3. i.e "A" Selected in ComboBox1, "Catagory1 - Release a", "Catagory2 - Release b", "Catagory3 - Release c", Will appear in comboBox2 Can anyone help ? Many Thanks Matt ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Combobox problem .
Matt, try s'thing like:
Option Explicit Dim rngData As Range Private Sub ComboBox1_Change() With ComboBox2 .List = Combinations(ComboBox1) If .ListCount 0 Then .ListIndex = 0 End With End Sub Private Sub UserForm_Initialize() Set rngData = ThisWorkbook.Worksheets(1).Range("tbl") With ComboBox1 .List = Uniques(rngData.Columns(1).Value) .ListIndex = 0 End With End Sub Function Uniques(v) Dim itm, res, i& Dim col As Collection On Error Resume Next Set col = New Collection For Each itm In v col.Add itm, CStr(itm) Next ReDim res(1 To col.Count) For i = 1 To col.Count res(i) = col(i) Next Uniques = res End Function Function Combinations(v) Dim itm, res, i& Dim col As Collection On Error Resume Next Set col = New Collection For Each itm In rngData.Columns(1).Cells If itm.Value = v.Value Then col.Add itm(1, 2) & itm(1, 3) Next ReDim res(1 To col.Count) For i = 1 To col.Count res(i) = col(i) Next Combinations = res End Function keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool mattis2k wrote: Hi, I have a problem in excel.. I have ComboBox1 which contains A, B, C, D. and ComboBox2 which I want to dynamically changed based on the choice made in ComboBox1. ComboBox1 is already a filtered selection of column A A1 A2 A3 A Catagory 1 Release a A Catagory 2 Release b A Catagory 3 Release c B Catagory 1 Release a B Catagory 2 Release b C Catagory 1 Release a D Catagory 1 Release a So when I choose A in ComboBox1 I want combobox2 to show a concatination of A2 and A3. i.e "A" Selected in ComboBox1, "Catagory1 - Release a", "Catagory2 - Release b", "Catagory3 - Release c", Will appear in comboBox2 Can anyone help ? Many Thanks Matt ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ComboBox problem | Excel Discussion (Misc queries) | |||
Problem Using Delete Key Because of Combobox | Excel Discussion (Misc queries) | |||
Excel combobox problem | Excel Discussion (Misc queries) | |||
Activation combobox problem. | Excel Programming | |||
ComboBox Problem | Excel Programming |