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/