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

spazmonkey

New Member
Joined
Dec 9, 2004
Messages
40
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
 
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
 
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
 
Upvote 0
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:
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
 
Upvote 0
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.

Code:
    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

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
 
Upvote 0
try
Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,922
Messages
6,175,384
Members
452,639
Latest member
RMH2024

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top