2 droplist

[davidson]

I would like to create 2 droplist in the excel. The value of the droplist would be obtained from a table. The table would be in the following format:

A 2
B 7
A 12
C 8
A 9
B 15
B 17

So for the first droplist, I would like to have the values A, B, C for the user to select (without duplicate). The value of second droplist would then depend on what is chosen by the user in first droplist. For example, if the user chooses A in the 1st droplist, I would like to have 2,12, 9 in the 2ns droplist for the user to select. It is possible or difficult to do this?

Thanks

 

[davidson]

Hi Jindon,

This code does not work and even the first ComboBox has errors. Would you mind if I send you my code on this?

Thanks

How about

Private dic As Object

Private Sub Worksheet_Activate
Dim r As Range, w()
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
With Sheets("Sheet1")
    For Each r In Range("a1",Range("a" & Rows.Count).End(xlUp))
        If Not IsEmpty(r) Then
            If Not dic.exists(r.Value) Then
                ReDim w(0) : w(0) = r.Offset(,1).Value
                dic.add r.Value, w
            Else
                w = dic(r.Value)
                ReDim Preserve w(UBound(w) + 1)
                w(UBound(w)) = r.Offset(,1).Value
                dic(r.Value) = w
            End If
        End If
    Next
End With
Me.ComboBox1.List = dic.keys
End Sub

Private Sub ComboBox1_Change()
Me.ComboBox2.Clear
Me.ComboBox3.Clear
Me.ComboBox4.Clear
With Me
   If .ComboBox1.ListIndex > -1 Then
      .ComboBox2.List = dic(.ComboBox1.Value)
   End If
End With
End Sub

Private Sub ComboBox2_Change()
Me.ComboBox3.Clear
Me.ComboBox4.Clear
With Me
    If .ComboBox1.ListIndex > -1 And .ComboBox2.ListIndex > -1 Then
        .ComboBox3.List = Array("Yes","No")
    End If
    .ComboBox3.ListIndex = -1
End With
End Sub

Private Sub ComboBox3_Change()
Dim a, x, y, i As Long, b(), n As Long
Me.ComboBox4.Clear
If Me.ComboBox3.Value <> "Yes" Then
    Me.Combobox4.Clear
    Exit Sub
End If
If Me.ComboBox1.ListIndex > -1 And Me.ComboBox2.ListIndex > -1 Then
    x = Me.ComboBox1.Value : y = Me.ComboBox2.Value
    a = Sheets("Sheet2").Range("a1").CurrentRegion.Resize(,3).Value
    For i = 1 To UBound(a,1)
         If a(i,1) = x And a(i,2) = y Then
            n = n + 1
            ReDim Preserve b(1 To n)
            b(n) = a(i,3)
        End If
    Next
End If
If n > 0 Then Me.ComboBox4.List = b
End Sub

 

[jindon]

Hi Jindon,

This code does not work and even the first ComboBox has errors. Would you mind if I send you my code on this?

Thanks

That's hardly understand, because the first part is exactly the same as the code before.

Can you tell me which line and what kind of error did you get?

 

[davidson]

The first comboBox displayed wrong content for the user to choose. And therefore there is an error in the sub comboBox1_change in the line:
.ComboBox2.List = dic(.ComboBox1.Value)

So i used back the old code for that part. Is there any way that I can send you my file for you to have a look.

 

[jindon]

If you have set rowsource or other data source property, you need to delete them...

Private dic As Object

Private Sub Worksheet_Activate
Dim r As Range, w()
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
With Sheets("Sheet1")
    For Each r In Range("a1",Range("a" & Rows.Count).End(xlUp))
        If Not IsEmpty(r) Then
            If Not dic.exists(r.Value) Then
                ReDim w(0) : w(0) = r.Offset(,1).Value
                dic.add r.Value, w
            Else
                w = dic(r.Value)
                ReDim Preserve w(UBound(w) + 1)
                w(UBound(w)) = r.Offset(,1).Value
                dic(r.Value) = w
            End If
        End If
    Next
End With
Me.ComboBox1.List = dic.keys
Me.ComboBox3.List = Array("Yes","No")
End Sub

Private Sub ComboBox1_Change()
Me.ComboBox2.Clear
Me.ComboBox3.Clear
Me.ComboBox4.Clear
With Me
   If .ComboBox1.ListIndex > -1 Then
      .ComboBox2.List = dic(.ComboBox1.Value)
   End If
End With
End Sub

Private Sub ComboBox2_Change()
Me.ComboBox3.Clear
Me.ComboBox4.Clear
With Me
    If .ComboBox1.ListIndex > -1 And .ComboBox2.ListIndex > -1 Then
        .ComboBox3.List = Array("Yes","No")
    End If
    .ComboBox3.ListIndex = -1
End With
End Sub

Private Sub ComboBox3_Change()
Dim a, x, y, i As Long, b()
Me.ComboBox4.Clear
If Me.ComboBox3.Value <> "Yes" Exit Sub
If (Me.ComboBox1.ListIndex > -1) * (Me.ComboBox2.ListIndex > -1) Then
    x = Me.ComboBox1.Value : y = Me.ComboBox2.Value
    With Sheets("Sheet2")
        a = .Range("a1").CurrentRegion.Resize(,3).Value
        For i = 1 To UBound(a,1)
            If (a(i,1) = x) * (a(i,2) = y) Then
                n = n + 1
                ReDim Preserve b(1 To n)
                b(n) = a(i,3)
            End If
        Next
    End With
End If
If n > 0 Then Me.ComboBox4.List = b
End Sub

 

[davidson]

I have checked those. But first combobox does not work and therefore the error still exists.

Also, in combobox3_Change, If Me.ComboBox3.Value <> "Yes" Exit Sub
has error.

Thanks, Jindon.

 

[jindon]

I have checked those. But first combobox does not work and therefore the error still exists.

Also, in combobox3_Change, If Me.ComboBox3.Value <> "Yes" Exit Sub
has error.

Thanks, Jindon.

OOps

That like should read as

If Me.ComboBox3.Value <> "Yes" Then Exit Sub

Can you explain the situation a bit more

1) Is list of ComboBox1 populated?
2) ComboBox2?

If none of the created, I need the sheet layouts...

 

[davidson]

What does populated mean? I do not enetr anything in Properties in combobox1 and combobox2.

Can I send you my excel file? My email is chengkithung@hotmail.com. Or how can I upload it onto the web?
Thanks, Jindon

I have checked those. But first combobox does not work and therefore the error still exists.

Also, in combobox3_Change, If Me.ComboBox3.Value <> "Yes" Exit Sub
has error.

Thanks, Jindon.

OOps

That like should read as

If Me.ComboBox3.Value <> "Yes" Then Exit Sub

Can you explain the situation a bit more

1) Is list of ComboBox1 populated?
2) ComboBox2?

If none of the created, I need the sheet layouts...