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

 

[erik.van.geit]

You're WELCOME !!
enjoy the Board

TIP: generally it's safer starting a new thread when you want to get more opinions about your question
you can always add a link to another thread to tell the others what you are working with

 

[Trial_and_error]

Another question about this topic. Can Eric's code also be applied on comboboxes placed in worksheets(listboxes would be even better)? I personally don't like using userforms.

Thanks for any help

Trial and Error

 

[erik.van.geit]

yep!
it's almost the same code to my sense (using controls from CONTROLS-toolbar)

try out a bit and feel free to ask more if needed

greetings from Belgium,
Erik

 

[roscoe]

Based on everything I've read, this code does pretty much what I need. I have a requirement to calculate travel costs (hotel and per Diem) based on a government data file. Input State, then City, then season (which I will have to create by concatenating start/end dates from two separate columns).

However, I'm too rusty in VB to understand where you even start to run this code and therefore how it performs (my wishes below may already be how it works...). But here are my 'druthers:

1) Would prefer to load the drop down boxes automatically so no code has to run first (run at file open in other words). Like a web page, let the user just go straight to combobox 1 (State) and start entering.
2) Hide the second and third combo boxes until the previous one has a valid entry selected
3) An easy way to back up if you select the wrong entry on the previous combobox.
4) Once the correct line from the data base is selected, I need two sets of data placed in two cells on the spreadsheet. Not a reference but the actual data (because the data changes and I need that snapshot in time preserved)

Can somebody help (1) step me running this code (i.e. the calls, the userform, whatever) and (2) help me modify this code to meet my 'druthers?

Thanks!

'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

[/quote]

 

[Norie]

Could you start a new thread?

 

[roscoe]

I did...no response so far

 

[Norie]

Well bump the new thread.

 

[Yazdani]

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

i use this code to use conditional selection in two comboxes where foods, beverages..... are tables containing data related to the table name, so if i choose/select FOODS in cmbbx1, i get RICE,Potatoes... in cmbbx2. i think it might give you an idea, you have to have named tables in the worksheet! and adapt this code to your worksheet of course!

 Private Sub UserForm_Initialize()
    With Me.ComboBox1
        .List = Sheets("ITEMS").Range("CATEGORIES").Value
    End With
End Sub

Private Sub ComboBox1_Change()
    If ComboBox1.ListIndex = -1 Then
        ComboBox2.Clear
        Exit Sub
    End If
    With Worksheets("ITEMS")
        ComboBox2.List = .Range(Choose(ComboBox1.ListIndex + 1, "FOODS", "BEERS", "ALCOHOLS", "BEVERAGES")).Value
    End With
End Sub