2 droplist

davidson

New Member
Joined
Oct 19, 2006
Messages
32
I would like to create 2 droplist in the excel. The value of the droplist would be obtained from a table. The table would be in the following format:

A 2
B 7
A 12
C 8
A 9
B 15
B 17

So for the first droplist, I would like to have the values A, B, C for the user to select (without duplicate). The value of second droplist would then depend on what is chosen by the user in first droplist. For example, if the user chooses A in the 1st droplist, I would like to have 2,12, 9 in the 2ns droplist for the user to select. It is possible or difficult to do this?

Thanks
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
I wrote the code for this already though
paste the code onto sheet module
select other sheet once and get back to the sheet in question
Code:
Private dic As Object

Private Sub Worksheet_Activate
Dim r As Range
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare

For Each r In Range("a1",Range("a" & Rows.Count).End(xlUp))
   If Not IsEmpty(r) Then
      If Not dic.exists(r.Value) Then
         dic.add r.Value, r.Offset(,1).Value
      Else
         dic(r.Value) = dic(r.Value) & "," & r.Offset(,1).Value
      End If
   End If
Next
With Range("d1")
   .Value = ""
   With .Validation
      .Delete
      .Add type:=xlValidateList, formula1:=Join(dic.keys,",")
   End With
   .Select
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
With Target.Cells(1,1)
   If .Address <> "D1" Then Exit Sub
   If IsEmpty(.Value) Then Exit Sub
End With
Application.EnableEvents = False
With Range("e1")
   .Value = ""
   With .Validation
      .Delete
      .Add type:=xlValidateLIst, formula1:=dic(Target.Value)
      .Select
   End With
End With
End Sub
 

davidson

New Member
Joined
Oct 19, 2006
Messages
32
Thanks. But it seems that this line does not work:
.Add Type:=xlValidateList, Formula1:=Join(dic.keys, ",")

I am sorry that I am just the beginner of the excel stuff. Sorry for my stupidity.

Thanks a lot. Also, is it possible to set the possible selected value of a combo box based on the value of another combo box? For example, if the user selects A in first combo box, the user can only be chosen a limited value in the 2nd combo box. Thanks
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Where are your comboboxes?

On the sheet? or on the form?

and which column does the list reside?
 

davidson

New Member
Joined
Oct 19, 2006
Messages
32

ADVERTISEMENT

My combo box is on the sheet now. Is it better to put it on the form?

The list is in A1:A10 on that sheet in teh following format.


A1: A
A2: B
A3: B
...
A10: C

I also create a comboBox2 on the sheet as well. The value of combo Box 2 would then on B1:B10. However, I would like to restrict the value of the combo Box2 depending on the chosen value of Combo Box1 by the user. Thanks a lot for your quick response. Thanks
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
try
Code:
Private dic As Object

Private Sub Worksheet_Activate
Dim r As Range, w()
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare

For Each r In Range("a1",Range("a" & Rows.Count).End(xlUp))
   If Not IsEmpty(r) Then
      If Not dic.exists(r.Value) Then
         ReDim w(0) : w(0) = r.Offset(,1).Value
         dic.add r.Value, w
      Else
         w = dic(r.Value)
         ReDim Preserve w(UBound(w) + 1)
         w(UBound(w)) = r.Offset(,1).Value
         dic(r.Value) = w
      End If
   End If
Next
Me.ComboBox1.List = dic.keys
End Sub

Private Sub ComboBox1_Change()
With Me
   If .ComboBox1.ListIndex > -1 Then
      .ComboBox2.List = dic(.ComboBox1.Value)
   End If
End With
End Sub
 

davidson

New Member
Joined
Oct 19, 2006
Messages
32

ADVERTISEMENT

Jindon, you are great.It works now. Thanks
 

davidson

New Member
Joined
Oct 19, 2006
Messages
32
I have 1 last question.

Is it possible for the table )i.e. th source a1 to a10 and b1 to b10 in the different sheet from the sheet containing combo box? Is it difficult to change?

Jindon, are you working in IT industry? WHere are you in? US?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
I don't think you need 3
try this
Code:
Private dic As Object

Private Sub Worksheet_Activate
Dim r As Range, w(), z As String
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
With Sheets("Sheet1") '<- change "sheet1" to suite
   For Each r In .Range("a1",.Range("a" & Rows.Count).End(xlUp))
      If Not IsEmpty(r) Then
         z = r.Value & " ; " & r.Offset(,1).Value
         If Not dic.exists(z) Then
            ReDim w(0) : w(0) = r.Offset(,2).Value
            dic.add z, w
         Else
            w = dic(z)
            ReDim Preserve w(UBound(w) + 1)
            w(UBound(w)) = r.Offset(,2).Value
            dic(z) = w
         End If
      End If
   Next
End With
Me.ComboBox1.List = dic.keys
End Sub

Private Sub ComboBox1_Change()
With Me
   If .ComboBox1.ListIndex > -1 Then
      .ComboBox2.List = dic(.ComboBox1.Value)
   End If
End With
End Sub
 

davidson

New Member
Joined
Oct 19, 2006
Messages
32
Thanks a lot.

I am now thinking of adding a 3rd combo box and teh value of the third combo box would depend on the values chosen in 1st and 2nd combo box. Would that be compliacted?

For example, my table is like this:

A 2 TT
B 16 UU
C 3 KL
A 2 YG
A 1 CB
A 4 FG

So if the user chooses A in 1st combo box and 2 in the 2nd combox, the values available in the third combo box is UU and YG.

Is it difficult to change? Thanks


I'm in Tokyo it's 16:07 here
Excel is not my experty, but I'm really found of using it.
I'm designing/constructing Database using various tools...
Code:
Private dic As Object

Private Sub Worksheet_Activate
Dim r As Range, w()
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
With Sheets("Sheet1") '<- change "sheet1" to suite
   For Each r In .Range("a1",.Range("a" & Rows.Count).End(xlUp))
      If Not IsEmpty(r) Then
         If Not dic.exists(r.Value) Then
            ReDim w(0) : w(0) = r.Offset(,1).Value
            dic.add r.Value, w
         Else
            w = dic(r.Value)
            ReDim Preserve w(UBound(w) + 1)
            w(UBound(w)) = r.Offset(,1).Value
            dic(r.Value) = w
         End If
      End If
   Next
End With
Me.ComboBox1.List = dic.keys
End Sub

Private Sub ComboBox1_Change()
With Me
   If .ComboBox1.ListIndex > -1 Then
      .ComboBox2.List = dic(.ComboBox1.Value)
   End If
End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,113,847
Messages
5,544,637
Members
410,627
Latest member
georgealice
Top