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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
 
Upvote 0
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
 
Upvote 0
Where are your comboboxes?

On the sheet? or on the form?

and which column does the list reside?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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