Excel User Form... Filter Combo box based on other

[spazmonkey]

I am trying to create an excel user form.

The form consists of 3 or 4 combo boxes and a few text boxes.

There are two worksheets "Data" and "ComboInfo".

Worksheet "ComboInfo" has 4 columns to represent the drop-down data.

I want the user to select a value in combo1. This will then repopulate and filter the list in combo2 with rows that matching combo1. I want to repeat the process for the other combo boxes.

Once the combo boxes are completed and a few additional textboxes the data needs to applied to the "Data" worksheet.

My main stumbling block is Filtering the Combo2.AddItem.

Any advice would be gratefully appreciated

 

[JONESY]

Hi Jindon

Works lovely, Thankyou! Eriks VB cascades down and fills the combo boxes when there is only one entry could you get your code to do that?

Regards

Jonesy

 

[jindon]

JONESY,

code has been modified. try again

 

[manon]

Changes to cascading comboboxes code - part 2

Hi Erik,
thanks for your feedback. I have tried to make the changes you suggested to copy the unique range to another sheet and this is working. Only now the lists are not showing in my comboboxes anymore. Any idea what is going wrong?

About the sorting, I am not sure if it is related to the combobox thing, for me it is...
What I mean is the following. If we take your example below and I select England and next London, I would like the Surname column to be sorted ascending. And if I only select England the town column should be sorted. Does this make my question understandable?

Thanks,
Manon

 

[erik.van.geit]

Manon,

If you still need help, please post back.
you can try out the code here

jindon,
I tried to elaborate your idea to make it possible to expland to more comboboxes.
here is the code:

'cascading comboboxes :-)
'sources in corresponding columns
  'box1 = column1 ...
'several comboboxes (see N)
  'to expand:
    'add combobox on userform
    'Const N = number of boxes
    'add Private Sub ComboBox ..N.. _Change()
    
Option Explicit

Const N = 4
Public flag As Boolean
Private r As Range, dic As Object

Private Sub userform_initialize()
Dim x As Variant
Set dic = CreateObject("Scripting.Dictionary")
With Sheets("ComboInfo")
    For Each r In .Range(.Cells(2, 1), .Cells(65536, 1).End(xlUp))
        If Not IsEmpty(r) And Not dic.exists(r.Value) Then
            dic.Add r.Value, Nothing
        End If
    Next
End With
x = dic.keys
Me.ComboBox1.List = x
End Sub

Private Sub ComboBox1_Change()
update_comboboxes (1)
'general syntax
'update_comboboxes (Application.WorksheetFunction.Substitute(ActiveControl.Name, "ComboBox", ""))
End Sub
Private Sub ComboBox2_Change()
update_comboboxes (2)
End Sub
Private Sub ComboBox3_Change()
update_comboboxes (3)
End Sub

Sub update_comboboxes(nr As Integer)
Dim ws As Worksheet
Dim i As Integer
Dim check As Boolean
Dim x As Variant

Set ws = Worksheets("ComboInfo")
    For i = nr + 1 To N
    Controls("ComboBox" & i).Clear
    Next i
Set dic = CreateObject("Scripting.dictionary")
    With ws
        For Each r In .Range(.Cells(2, 1), .Cells(65536, 1).End(xlUp))
            For i = 1 To nr
            check = r.Offset(0, i - 1) = Me.Controls("ComboBox" & i).Value
            If check = False Then Exit For
            Next i
            If check And Not dic.exists(r.Offset(0, nr).Value) Then
                dic.Add r.Offset(, nr).Value, Nothing
            End If
        Next
    End With
    With Me.Controls("ComboBox" & nr + 1)
        x = dic.keys
        .List = x
        If .ListCount = 1 Then .ListIndex = 0
    End With
End Sub

Private Sub CommandButton1_Click()
Dim i As Integer
Dim LR As Long

LR = Cells(65536, 1).End(xlUp).Offset(1, 0).Row
For i = 1 To N
Sheets("Archive").Cells(LR, i) = Controls("ComboBox" & i)
Next i
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

kind regards,
Erik

 

[dxxxxp]

cascading comboxes (UserForm - VBA)

Hello,

first of all thanks for this code jindon and erik.van.geit - works like charm and its exactly what I was looking for for a long time.

Have small question though.

Im not a programmer so dont know how to solve my problerm but I hope is nothing hard.

Could you help me change this piece of code (below) in which you define list for CombBox. Would like to add one more ellement ("All") to certain ComboBoxe list (let's say to the 6th one), and make this first element on the dropdown list. Had read something on VBA help but dont know how to get this "object.Add" thing in order. Please help.

    With Me.Controls("ComboBox" & nr + 1)
        x = dic.keys
        .List = x
        If .ListCount = 1 Then .ListIndex = 0
    End With

Full code below - from jindon and erik.van.geit

'cascading comboboxes :-)
'sources in corresponding columns
  'box1 = column1 ...
'several comboboxes (see N)
  'to expand:
    'add combobox on userform
    'Const N = number of boxes
    'add Private Sub ComboBox ..N.. _Change()
   
Option Explicit

Const N = 4
Public flag As Boolean
Private r As Range, dic As Object

Private Sub userform_initialize()
Dim x As Variant
Set dic = CreateObject("Scripting.Dictionary")
With Sheets("ComboInfo")
    For Each r In .Range(.Cells(2, 1), .Cells(65536, 1).End(xlUp))
        If Not IsEmpty(r) And Not dic.exists(r.Value) Then
            dic.Add r.Value, Nothing
        End If
    Next
End With
x = dic.keys
Me.ComboBox1.List = x
End Sub

Private Sub ComboBox1_Change()
update_comboboxes (1)
'general syntax
'update_comboboxes (Application.WorksheetFunction.Substitute(ActiveControl.Name, "ComboBox", ""))
End Sub
Private Sub ComboBox2_Change()
update_comboboxes (2)
End Sub
Private Sub ComboBox3_Change()
update_comboboxes (3)
End Sub

Sub update_comboboxes(nr As Integer)
Dim ws As Worksheet
Dim i As Integer
Dim check As Boolean
Dim x As Variant

Set ws = Worksheets("ComboInfo")
    For i = nr + 1 To N
    Controls("ComboBox" & i).Clear
    Next i
Set dic = CreateObject("Scripting.dictionary")
    With ws
        For Each r In .Range(.Cells(2, 1), .Cells(65536, 1).End(xlUp))
            For i = 1 To nr
            check = r.Offset(0, i - 1) = Me.Controls("ComboBox" & i).Value
            If check = False Then Exit For
            Next i
            If check And Not dic.exists(r.Offset(0, nr).Value) Then
                dic.Add r.Offset(, nr).Value, Nothing
            End If
        Next
    End With
    With Me.Controls("ComboBox" & nr + 1)
        x = dic.keys
        .List = x
        If .ListCount = 1 Then .ListIndex = 0
    End With
End Sub

Private Sub CommandButton1_Click()
Dim i As Integer
Dim LR As Long

LR = Cells(65536, 1).End(xlUp).Offset(1, 0).Row
For i = 1 To N
Sheets("Archive").Cells(LR, i) = Controls("ComboBox" & i)
Next i
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

 

[jindon]

try

With Me.Controls("ComboBox" & nr + 1) 
        x = dic.keys 
        redim preserve x(ubound(x)+1)
        for i=ubound(x) to 1 step -1
            x(i)=x(i-1)
        next
        x(0)="All"
        .List = x 
        If .ListCount = 1 Then .ListIndex = 0 
End With

 

[dxxxxp]

"It's alive" !! works perfect !

Arigato jindon :]

 

[Sars]

Good afternoon everybody.

This is my first post onto this board, I have always found this site extremely helpful and would recommend to any newbee just starting to learn VBA.

I apologise for hi-jacking somebody elses thread, but from what I have seen so far this is exactly the solution I require for an ongoing project of mine.

Jindon, with regards your approach, how do I add a further two combo boxes.

I have used your example so far with success, but now need to expand it further, by adding two more comboboxes to your code. How do I do it?. Any help you may be able to give would be appreciated.

thanks

 

[erik.van.geit]

Hi, Sars
Welcome to the Board !!!!!

perhaps you missed the reply (on page 2 of this thread) with code to expand the number of comboboxes ?

kind regards,
Erik

 

[Sars]

Hi Erik,

sorry, I've not looked properly. Using the code provided it now works.It does exactly what I want.

Thank you for your help.