Forum Discussion
TheodoreHugo
Feb 23, 2025Copper Contributor
Multi-select dropdowns when answer effects several dependant dropdowns
Hello everyone, I'm just going to start by saying that while I'm fairly confident with basic excel formulas, this is my first venture into macros and VBA so please be patient with me The situation:...
Kidd_Ip
Feb 24, 2025MVP
Considering this:
Multi-Select Dropdown for Specific Questions:
Private Sub Worksheet_Change(ByVal Destination As Range)
Dim rngMultiSelect As Range
Dim rngSingleSelect As Range
Dim oldValue As String
Dim newValue As String
Dim DelimiterType As String
DelimiterType = ", "
If Destination.Count > 1 Then Exit Sub
On Error Resume Next
' Set the ranges for multi-select and single-select dropdowns
Set rngMultiSelect = Me.Range("A1:A10") ' Change this to your multi-select range
Set rngSingleSelect = Me.Range("B1:B10") ' Change this to your single-select range
On Error GoTo exitError
If Not Intersect(Destination, rngMultiSelect) Is Nothing Then
Call HandleMultiSelect(Destination, DelimiterType)
ElseIf Not Intersect(Destination, rngSingleSelect) Is Nothing Then
' Handle single-select dropdowns if needed
End If
exitError:
Application.EnableEvents = True
End Sub
Private Sub HandleMultiSelect(ByVal Destination As Range, ByVal DelimiterType As String)
Dim oldValue As String
Dim newValue As String
Dim arr() As String
Dim i As Integer
Application.ScreenUpdating = False
Application.EnableEvents = False
newValue = Destination.Value
Application.Undo
oldValue = Destination.Value
Destination.Value = newValue
If oldValue <> "" Then
If newValue <> "" Then
If oldValue = newValue Then
oldValue = ""
ElseIf InStr(1, oldValue, newValue) > 0 Then
arr = Split(oldValue, DelimiterType)
For i = 0 To UBound(arr)
If arr(i) = newValue Then
arr(i) = ""
End If
Next i
oldValue = Join(arr, DelimiterType)
Else
oldValue = oldValue & DelimiterType & newValue
End If
End If
Else
oldValue = newValue
End If
Destination.Value = oldValue
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Hiding Questions Based on Previous Answers:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim questionRange As Range
Set questionRange = Me.Range("C1:C73") ' Change to your question range
If Not Intersect(Target, questionRange) Is Nothing Then
Call HideQuestionsBasedOnAnswers
End If
End Sub
Private Sub HideQuestionsBasedOnAnswers()
Dim questionCell As Range
For Each questionCell In Me.Range("C1:C73") ' Change to your question range
' Example condition to hide question if answer is "No"
If questionCell.Value = "No" Then
questionCell.EntireRow.Hidden = True
Else
questionCell.EntireRow.Hidden = False
End If
Next questionCell
End Sub
TheodoreHugo
Feb 25, 2025Copper Contributor
Hello Kidd_Ip ,
Thank you for your response :)
I think I have done something wrong with the multi selection code. Its applied the single selection/multi selection to the correct cells, and the single selection ones work beautifully. But I'm struggling with the multi-selection ones, it keeps filling up with commas and won't let me clear the cell?
Also, I'm not sure how to apply the hide questions code. In my screenshot above D23 is a yes/no question and if the answer is 'no' I want to hide rows 24:25
Thank you for your time and patience