PDA

View Full Version : connected combobox



fra2309
10-31-2012, 02:15 AM
Hi all
I have a question regarding two or more combobox that I wanna connected somehow. It is not a new topic but I am not able to adapt the information of other posts because of my bery very basic knowledge of VBA.

I have in the sheet "data" a table whcih is the source of my data.
Then I put two combobox in the sheet2 that I renamed "View".
I need to connect the two combobox in a way that if I choose a value for combobox1, the combox2 will be updated somehow and will diplay me only those value which are relevant with the choice of combobox1.
Let's say that in combobox1 I can choose the continents: Europe, America, Asia..... for instance if I chose Europe, I would like combobox 2 to display only the European countries: Italy, Germany....
And the same if I do the other way around...if I choose Italy in combo2, I would like to have "Europe" as unique option in combo1.
I copy here the VBA code that I use to populate my combobox.
Private Sub cmdUpload_Click()
strSQL = "Select distinct [Continents] from [data$] Order by [Continents] "
closeRS
OpenDB
cmbContinents.Clear
rs.Open strSQL, cnn, adOpenKeyset, adLockPessimistic
If rs.RecordCount > 0 Then
Do While Not rs.EOF
cmbContinents.AddItem rs.Fields(0)
rs.MoveNext
Loop
Else
MsgBox "I was not able to find any unique Products.", vbCritical + vbOKOnly
Exit Sub
End If
'----------------------------------------------------------------------------------
strSQL = "Select distinct [Country] from [data$] Order by [Country] "
closeRS
OpenDB
cmbCountry.Clear
rs.Open strSQL, cnn, adOpenKeyset, adLockPessimistic
If rs.RecordCount > 0 Then
Do While Not rs.EOF
cmbCountry.AddItem rs.Fields(0)
rs.MoveNext
Loop
Else
MsgBox "I was not able to find any unique Products.", vbCritical + vbOKOnly
Exit Sub
End If
End Sub


Can you tell me how I have to change this code?
Thank you very much!