2 ComboBox

[haseft]

hi,
I have a userform with 2 listbox,
ListBox1, pupulate uniqe items from column B in Sheet1, workning well,

Need help with ComboBox2.
want to fill ComboBox2 with criteria choosen fron ComboBox1
ComboBox2 should show item from column A in Sheet1
Tanks,

Private Sub UserForm_Initialize()
Dim myCollection As Collection, cell As Range

On Error Resume Next
Set myCollection = New Collection
With ComboBox1
  .Clear
For Each cell In Sheets("Sheet1").Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
If Len(cell) <> 0 Then
  Err.Clear
  myCollection.Add cell.Value, cell.Value
  If Err.Number = 0 Then .AddItem cell.Value
End If
Next cell
End With
ComboBox1.ListIndex = 0

End Sub

Private Sub ComboBox1_Change()
'want to fill ComboBox2 with criteria choosen from ComboBox1
'ComboBox2 should show items from column A in Sheet1

With ComboBox2
.ColumnCount = 1
.ColumnWidths = "50"
.ColumnHeads = True
[B]'how ?[/B]


End With
End Sub

 

[haseft]

this is the Sheet1
textboxes data from column E, H and J in Sheet1 .

'TextBox1.value = data from clomn E

TextBox2.value = data from clomn H

TextBox3.value = data from clomn J

Year	Org	Orgnr	Group	Nummber	Desition	Amount	Copy	Datum1	Datum2
2 016	aaa		1		STY	0	2	2017-01-01	2019-12-30
2017	aaa		1	1 200 tkr	STY	900 000	1	2017-01-01	2019-12-31
2018	aaa		1		STY	900 000	1	2017-01-01	2019-12-31
2019	aaa		1		STY	900 000	1	2017-01-01	2019-12-31
2018	sss		3	500 tkr	STY	100 000	1	2018-06-15	2018-08-31
2015	bbb		3		SPG	25 000	2
2 016	bbb		3		SPG	19 000	1	2016-01-01	2016-12-31
2017	bbb		3	25 tkr	SPG	12 500	2	2017-01-01	2018-12-31
2018	bbb		3		SPG	12 500	2	2017-01-01	2018-12-31
2020	www		3	25 tkr	SPG	10 000	2	202-01-01	2020-12-31
2015	rrr		2		STY	12 000	2
2 016	rrr		2		STY	50 000	1	2016-01-01	2016-12-31
2017	rrr		2	52 tkr	STY	52 000	1	2017-01-01	2017-12-31
2018	rrr		2	54,6 tkr	STY	55 000	1	2018-01-01	2020-12-31
2019	rrr		2		STY	55 000	1	2018-01-01	2020-12-31
2020	rrr		2		STY	55 000	1	2018-01-01	2020-12-31
2019	yyy		2	35 tkr	SPG	0	2
2019	ooo		4	10 tkr	SPG	5 000	2	2019-01-01	2019-12-31
2018	qqq		4	50 tkr	Tommy	10 000	2	2018-08-01	2018-12-31
2019	qqq		4		SPG	0	2	2019-01-01	2019-12-31
2015	ppp		3		SPG	20 000	1
2 016	ppp		3		SPG	10 000	2	2016-10-01	2017-09-30
2017	ppp		3	60 tkr	SPG	10 000	2	2017-07-01	2019-06-30
2018	ppp		3		SPG	10 000	2	2017-07-01	2019-06-30
2019	ppp		3	10 tkr	SPG	10 000	2	2019-01-01	2019-12-31

 

[haseft]

This is Sheet1

 

[Fluff]

As col A has numbers, use this

   With UfDic(Me.ComboBox1.Value)
      Me.TextBox1 = .Item(Val(Me.ComboBox2.Value))(0)
      Me.TextBox2 = .Item(Val(Me.ComboBox2.Value))(1)
      Me.TextBox3 = .Item(Val(Me.ComboBox2.Value))(2)
   End With

 

[haseft]

workning perfec,
Thank a lot Fluff

 

[Fluff]

You're welcome & thanks for the feedback