Dynamic dropdowns Mail 13 Feb 2014 12:00 AM PST

[By_Sal]

Hello , thanks for the help you have given me , even if I do not participate much in the Forum , I take many ideas from the forum.
Sorry, but the text is translated by google , do not know the English
Therefore, reference to :
Dynamic (Cascading ) Dropdowns That reset on change
Posted: February 13, 2014 12:00 AM PST
I do not know if it was already treated the subject but some considerations related to the "Validate " I was made to address the problem in a different way , this is my small contribution .
Even for the limits imposed by Validation, " Sequential Search " and " Limit 1000 records visible "
My method of research used a Waterfall " Combobox ActiveX " on the sheet for each field , and only one this list is an example:





This is the only list for selection:




As you can see only one column for Field Names with repeated until the change of the next field , we move to the VBA I used, forgive the limited , but it is very functional.

 Private Sub Worksheet_Activate ()
Dim x & , n & , v, col As Collection
 Set col = New Collection
 On Error Resume Next
 n = Cells ( Rows.Count , 27) . End ( xlUp ) . Row
 For x = 2 To n
 col.Add Cells ( x , 27) . Value, CStr ( Cells ( x , 27) . Value)
 Next x
 n = col.Count
 ComboBox1.Clear
 For Each v In with
 ComboBox1.AddItem with ( v)
 Next v
 ComboBox1.Visible = False
 ComboBox2.Visible = False
 ComboBox3.Visible = False
 ComboBox4.Visible = False
end Sub
Private Sub Worksheet_SelectionChange (ByVal Target As Range)
 If Not Intersect ( Target, [ f1 ] ) Is Nothing Then Clear
 If Not Intersect ( Target, [ b3 ] ) Is Nothing Then ComboBox1.Visible = True
end Sub
Private Sub ComboBox1_Click ()
Dim x & , r & , & lng1 , nom $
 with Me.ComboBox2
 . Clear
 r = Cells ( Rows.Count , 28) . End ( xlUp ) . Row
 For lng1 = 2 To r
 If Cells ( lng1 , 27) . Me.ComboBox1.Text Then Value =
 nom = Cells ( lng1 , 28) . Value
 If nom = Cells ( lng1 - 1 , 28) . Value Then Else . AddItem Cells ( lng1 , 28) . Value
 end If
 Next
 end With
 ComboBox1.Visible = False
 ComboBox2.Visible = True
end Sub
Private Sub ComboBox2_Click ()
Dim x & , r & , & lng1 , nom $
 with Me.ComboBox3
 . Clear
 r = Cells ( Rows.Count , 29) . End ( xlUp ) . Row
 For lng1 = 2 To r
 If Cells ( lng1 , 27) . Me.ComboBox1.Text And Value = Cells ( lng1 , 28) . Me.ComboBox2.Text Then Value =
 nom = Cells ( lng1 , 29) . Value
 If nom = Cells ( lng1 - 1 , 29) . Value Then Else . AddItem Cells ( lng1 , 29) . Value
 end If
 Next
 end With
 ComboBox2.Visible = False
 ComboBox3.Visible = True
end Sub
Private Sub ComboBox3_Click ()
Dim x & , r & , & lng1 , nom $
 with Me.ComboBox4
 . Clear
 r = Cells ( Rows.Count , 30) . End ( xlUp ) . Row
 For lng1 = 2 To r
 If Cells ( lng1 , 27) . Me.ComboBox1.Text And Value = Cells ( lng1 , 28) . Me.ComboBox2.Text And Value = Cells ( lng1 , 29) . Me.ComboBox3.Text Then Value =
 nom = Cells ( lng1 , 30) . Value
 If nom = Cells ( lng1 - 1 , 30) . Value Then Else . AddItem Cells ( lng1 , 30) . Value
 end If
 Next
 end With
 ComboBox3.Visible = False
 ComboBox4.Visible = True
end Sub
Private Sub ComboBox4_Click ()
 ComboBox4.Visible = False
end Sub
This Macro Cleaning Fields
Sub Clear ()
Dim x & , n & , v, col As Collection
 Foglio1.Select
 Range (" B3: E3 "). ClearContents
 Set col = New Collection
 On Error Resume Next
 n = Cells ( Rows.Count , 27) . End ( xlUp ) . Row
 For x = 2 To n
 col.Add Cells ( x , 27) . Value, CStr ( Cells ( x , 27) . Value)
 Next x
 n = col.Count
 Foglio1.ComboBox1.Clear
 For Each v In with
 Foglio1.ComboBox1.AddItem with ( v)
 Next v
 Foglio1.ComboBox1.Visible = False
 Foglio1.ComboBox2.Visible = False
 Foglio1.ComboBox3.Visible = False
 Foglio1.ComboBox4.Visible = False
end Sub

To add a new item just add it to the list , in the sequence of the fields.
I used the same system of inserting multiple rows , making the move Combobox on the selected cell.

By Sal

 

[Gabriel Srebro]

Thanks Sal!
I enhaced it sorting the combo objects using the following code before the lasta for each loop:

   For i = 1 To col.Count - 1
        For j = i + 1 To col.Count
            If col(i) > col(j) Then
                'store the lesser item
               vTemp = col(j)
                'remove the lesser item
               col.Remove j
                're-add the lesser item before the
               'greater Item
               col.Add vTemp, vTemp, i
            End If
        Next j
    Next i

It is important to add this declarations first

    Dim i As Long, j As Long
    Dim vTemp As Variant