User Forms with Dependent Drop-down Selections

Hi all,

I have two doubts I would like to share to the wider community for help. I am trying to enable multiple dependent drop-down forms with multiple selections (combining some VBA code) to collect some data. Nonetheless, I am facing two issues when doing so.

I have a drop-down in which you can select from 1 to 9 options. This should trigger a second drop-down based on the concatenated sub-categories made in those selections on the next column (e.g.: If you selected “fish” and “vegetables”; in the next column you should have all fishes and all vegetables inside the dropdown [mackerel, salmon, cucumber, spinach]). This, which can be intuitive, has been a nightmare on the backend:I had to go to a programming language (R in my case) to create all the different 511 possible combinations of items from the selections, putting all titles alphabetically ordered and in named_ranges style.I pasted that creation to Excel, the 511 different named ranges and automatically created a formula for crafting 511 named ranges taking into account the different lengths.Finally, I created on the main table a helper column ordering the first selection alphabetically (with yet again another VBA formula) so that I could match with =INDIRECT(“helper_column_cell”) the different possible concatentations. A long journey given the fact I did not see a way to seamlessly join different named ranges…A second issue has to do with the multiple selection. I achieved having the dropdown with the right data depending on the first 1 to 9 combinations input; enabling me to select multiple items from the resulting list, concatenating them with commas and not allowing repetitions. Nonetheless, each input requires reopening the drop-down again, and I would like that to be done in one shot. I thought about a user form, but it looks as adapting the options to the 511 possible combinations is dreamland. Below you see the code I do have for now:Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDataMapping As Range
Dim Oldvalue As String
Dim Newvalue As String
Dim arr() As String
Dim Output As String
Dim i As Integer

‘ Set the range for the multiple drop-down (columns O and R in this case)
Set rngDataMapping = Intersect(Me.Range(“O:O, R:R, S:S”), Target)

If Not rngDataMapping Is Nothing Then
Application.EnableEvents = False
On Error GoTo Exitsub
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub Else
If Target.Value = “” Then GoTo Exitsub Else
Application.Undo
Oldvalue = Target.Value
Application.Undo
Newvalue = Target.Value

‘ Check if the new value already exists
If InStr(1, Oldvalue, Newvalue) = 0 Then
If Oldvalue = “” Then
Target.Value = Newvalue
Else
arr = Split(Oldvalue, “, “)
Output = “”
For i = LBound(arr) To UBound(arr)
If arr(i) <> Newvalue Then
If Output = “” Then
Output = arr(i)
Else
Output = Output & “, ” & arr(i)
End If
End If
Next i
If Output = “” Then
Output = Newvalue
Else
Output = Newvalue & “, ” & Output
End If
Target.Value = Output
End If
End If
End If

Exitsub:
Application.EnableEvents = True
Exit Sub
End Sub

Do you have any recommendation on better and more efficient solutions to Problem 1? Is there any way to sort out the issue on problem 2 to allow the users to do multiple selections without the drop-down collapsing with any new input? I would really appreciate your mastery and comments!

Hi all, I have two doubts I would like to share to the wider community for help. I am trying to enable multiple dependent drop-down forms with multiple selections (combining some VBA code) to collect some data. Nonetheless, I am facing two issues when doing so. I have a drop-down in which you can select from 1 to 9 options. This should trigger a second drop-down based on the concatenated sub-categories made in those selections on the next column (e.g.: If you selected “fish” and “vegetables”; in the next column you should have all fishes and all vegetables inside the dropdown [mackerel, salmon, cucumber, spinach]). This, which can be intuitive, has been a nightmare on the backend:I had to go to a programming language (R in my case) to create all the different 511 possible combinations of items from the selections, putting all titles alphabetically ordered and in named_ranges style.I pasted that creation to Excel, the 511 different named ranges and automatically created a formula for crafting 511 named ranges taking into account the different lengths.Finally, I created on the main table a helper column ordering the first selection alphabetically (with yet again another VBA formula) so that I could match with =INDIRECT(“helper_column_cell”) the different possible concatentations. A long journey given the fact I did not see a way to seamlessly join different named ranges…A second issue has to do with the multiple selection. I achieved having the dropdown with the right data depending on the first 1 to 9 combinations input; enabling me to select multiple items from the resulting list, concatenating them with commas and not allowing repetitions. Nonetheless, each input requires reopening the drop-down again, and I would like that to be done in one shot. I thought about a user form, but it looks as adapting the options to the 511 possible combinations is dreamland. Below you see the code I do have for now:Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDataMapping As Range
Dim Oldvalue As String
Dim Newvalue As String
Dim arr() As String
Dim Output As String
Dim i As Integer

‘ Set the range for the multiple drop-down (columns O and R in this case)
Set rngDataMapping = Intersect(Me.Range(“O:O, R:R, S:S”), Target)

Exitsub:
Application.EnableEvents = True
Exit Sub
End SubDo you have any recommendation on better and more efficient solutions to Problem 1? Is there any way to sort out the issue on problem 2 to allow the users to do multiple selections without the drop-down collapsing with any new input? I would really appreciate your mastery and comments! Read More

Cart

Cart