dependent Combo boxes on a user form

Nautilus · Jun 30, 2004

Hello to all!

I'm having trouble creating dependant combo boxes on a user form. In the first combo box user selects type, e.g. DM604 and the following combo box should then list all the possible speed options for that type. As there are a number of different types and each have multiple speed options I'm using .Rowsource = "XXX!TYPES" for the first combo box (TYPES = named range). In my workbook, there is a named range for all the speed options, e.g. there is a named range "DM604" which refers to cells "YYY!Q8:V11" and contains speed options for type DM604. Using data validation method it would be easy but my executives want to use user forms. Can anyone help me to create dependent combo boxes. I've searched for a solution for a couple of hours but could't find one. Thanks in advance!

p.s. I'm quite a newbie when it comes to excel and vba so explicit words of advice would be much appreciated!

Andrew Poulsom · Jun 30, 2004

See if this helps you:

http://216.92.17.166/board2/viewtopic.php?t=66755

Nautilus · Jun 30, 2004

The main problem is this

I read through the link(s) but I couldn't figure out a solution. The main problem is that the named range of different speed options is not a single row or column (and can not be changed, would propably require too much work). I tried to come up with a code that would read the value in combobox1, then go through each cell in the named range of that particular name and add items one by one in a some kind of loop... Could this be accomplished some how?

And thanks a lot for Your help!

edit:

Private Sub TypeMain_Change()
SpeedMain.RowSource = TypeMain.Value
End Sub

brings the values in first column of the named range to combobox2 but there are more columns than one in the speed options range.

Andrew Poulsom · Jun 30, 2004

I set up a spreadsheet like this:

BOOK2

A

B

C

D

E

1

List

ItemA

ItemB

2

ItemA

ItemA1

ItemA4

ItemB1

ItemB4

3

ItemB

ItemA2

ItemA5

ItemB2

ItemB5

4

ItemA3

ItemA6

ItemB3

ItemB6

5

6

List

=Sheet1!$A$2:$A$3

7

ItemA

=Sheet1!$B$2:$C$4

8

ItemB

=Sheet1!$D$2:$E$4

Sheet1

with the named ranges as shown.

Then I used this code:

Code:

Private Sub ComboBox1_Change()
    Dim Rng As Range
    Dim r As Integer
    Dim c As Integer
    Set Rng = Range(ComboBox1.Value)
    ComboBox2.Clear
    For c = 1 To Rng.Columns.Count
        For r = 1 To Rng.Rows.Count
            ComboBox2.AddItem Rng.Cells(r, c)
        Next r
    Next c
End Sub

Hope that helps.

Nautilus · Jun 30, 2004

Thanks Andrew,

That worked out great!

I would like to ask one more thing in order to fine tune the user interface.
Values in the named range are set up like this (I propably should have posted this right away)

DM604 G5 7,5 9,5 14 16 2
DM604 G6 7,5 9,5 19 16 30
DM604 G7 9,5 22 30
DM604 G8 24 36

(hope that this shows up so that it makes sense, btw G"X" is the class and the named range contains values from 7,5 to 36, not the type or the class of course...)

So, there are blank cells and cells that contain the same number. Is there any way that the procedure would add only unique number values (no blanks), preferably in ascending order? Or is it too difficult? Anyway, thanks again for your time and effort,

cheers!

Nautilus · Jun 30, 2004

It came to my mind that sort method could be used to variable Rng, so that the original values would not get mixed up, but the question is how

I think it should be something like this

Rng.Sort(xxx, xlAscending)

but where should I put it in following code

Code:

Private Sub TypeMain_Change()
On Error GoTo Ex
    Dim Rng As Range
    Dim r As Integer
    Dim c As Integer
    Set Rng = Range(TypeMain.Value)
    SpeedMain.Clear
    For c = 1 To Rng.Columns.Count
        For r = 1 To Rng.Rows.Count
            SpeedMain.AddItem Rng.Cells(r, c)
        Next r
    Next c
    
Ex:
With SpeedMain
.RowSource = ""
.Text = ""
End With

End Sub

And once again, thanks for the help (in advance)!

Nautilus · Jun 30, 2004

Sorry to post again on the same subject, couldn't use edit any more...

Well, I tried to get the sort method work but I'm afraid that the range must be copied to a temporary sheet if it must be sorted, any pointers on how this should be done? And how should the code for sort be written when data that must be sorted is in this type of format:

Code:

4,5	7,1	7,5	12		  24
4,5	9	  7,5	15	17   30
4,5	11		    15	19   30
	   12		    17	24   34

edit: problem solved, thanks to Andrew for Helping!

Andrew Poulsom · Jun 30, 2004

With this spreadsheet:

ComboBoxSynchUnique.xls

A

B

C

D

E

F

G

1

List

ItemA

7.5

9.5

14

16

2

ItemA

7.5

9.5

19

16

30

3

ItemB

ItemA

9.5

22

30

4

ItemA

24

36

5

ItemB

12.5

14.5

19

21

7

6

ItemB

12.5

14.5

24

21

35

7

ItemB

14.5

27

35

8

ItemB

29

41

9

10

List

=Sheet1!$A$2:$A$3

11

ItemA

=Sheet1!$C$1:$G$4

12

ItemB

=Sheet1!$C$5:$G$8

Sheet1

This code will create in ComboBox2 a list of unique items from the range selected in ComboBox1:

Code:

Private Sub ComboBox1_Change()
    Dim Rng As Range
    Dim c As Range
    Dim TheList As New Collection
    Dim i As Long
    Dim j As Long
    Dim Temp As Variant
    Dim Item
    Set Rng = Range(ComboBox1.Value)
    ComboBox2.Clear
'   Create unique list
    On Error Resume Next
    For Each c In Rng
        TheList.Add c.Value, CStr(c.Value)
    Next c
    On Error GoTo 0
'   Sort the list
    If TheList.Count > 1 Then
        For i = 1 To TheList.Count - 1
            For j = i + 1 To TheList.Count
                If TheList(i) > TheList(j) Then
                    Temp = TheList(j)
                    TheList.Remove (j)
                    TheList.Add Temp, CStr(Temp), i
                End If
            Next j
        Next i
    End If
    For Each Item In TheList
        ComboBox2.AddItem Item
    Next Item
End Sub

dependent Combo boxes on a user form

Nautilus

Board Regular

Excel Facts

Andrew Poulsom

MrExcel MVP

Nautilus

Board Regular

Andrew Poulsom

MrExcel MVP

Nautilus

Board Regular

Nautilus

Board Regular

Nautilus

Board Regular

Andrew Poulsom

MrExcel MVP

Similar threads

Forum statistics

Share this page

dependent Combo boxes on a user form

Board Regular

Excel Facts

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

Board Regular

Board Regular

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock