Complex Combobox problem .

[mattis2k]

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

 

[keepitcool]

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 >