Combobox

[dila1234]

hi,

i have a database and want to make summary from it. i have two combo boxes, and i want to fill the combo boxes with details in column F and column K without duplicate items. the database will be updated daily.

For 1 combo box, i have successfully make it with no duplicate value. But when i try to add 2 combo box, i have faced some issue. there are no output at the combo box at all.
I have try to seek advice but no response.

Help me

 

[chirayu]

sample file please

 

[AlanSidman]

crossposted: https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1211279-combobox

 

[dila1234]

Below is the failed coding:

Private Sub ComboBox1_Change()

Dim I As Long
For I = 2 To Db.Range("F10000").End(xlUp).Offset(1, 0).Row
x = Application.WorksheetFunction.CountIf(Db.Range("F" & 2, "F" & I), Db.Cells(I, 1).Value)
If x = 1 Then
Me.ComboBox1.AddItem Db.Cells(I, 1).Value
End If
Next I

End Sub

Private Sub ComboBox2_Change()

Dim I As Long
For I = 2 To Db.Range("K10000").End(xlUp).Offset(1, 0).Row
x = Application.WorksheetFunction.CountIf(Db.Range("K" & 2, "K" & I), Db.Cells(I, 1).Value)
If x = 1 Then
Me.ComboBox1.AddItem Db.Cells(I, 1).Value
End If
Next I

End Sub

 

[dila1234]

sample file please

thank you for your response. Below is the sample file.

 

[Ajesh]

You missed the file again but from first look, I think you need to change this line. You are updating Combobox 1 in combobox 2 code.

Me.ComboBox1.AddItem Db.Cells(I, 1).Value

to

Me.ComboBox2.AddItem Db.Cells(I, 1).Value

 

[dila1234]

You missed the file again but from first look, I think you need to change this line. You are updating Combobox 1 in combobox 2 code.

Me.ComboBox1.AddItem Db.Cells(I, 1).Value

to

Me.ComboBox2.AddItem Db.Cells(I, 1).Value

Thank you Ajesh. I have change it but it didn't work still. Sample file is the excel itself? Attached is the file.

 

[Ajesh]

Not sure if the file contains all relevant code to test as there are no declaration etc. You are using Db as a worksheet object but I don't see it being declared anywhere. However, from little that we have (not tested as declarations are missing) I can see below issues here:

1. I would suggest to populate the Combobox (at least for the first time) on UserForm Initialize so that it has values readily available when form loads.
2. When you are checking counts of value in Column K and F you need to give right criteria value. You are looking dups in column K and F and giving criteria as Col 1. You need to change the line as below:
For ComboBox1 -

For I = 2 To Db.Range("F10000").End(xlUp).Offset(1, 0).Row
    x = Application.WorksheetFunction.CountIf(Db.Range("F" & 2, "F" & I), Db.Cells(I, 6).Value)
    If x = 1 Then
        Me.ComboBox1.AddItem Db.Cells(I, 6).Value
    End If
Next I

For ComboBox1 -

For I = 2 To Db.Range("K10000").End(xlUp).Offset(1, 0).Row
    x = Application.WorksheetFunction.CountIf(Db.Range("K" & 2, "K" & I), Db.Cells(I, 11).Value)
    If x = 1 Then
        Me.ComboBox2.AddItem Db.Cells(I, 11).Value
    End If
Next I

Hope it helps.
Thanks