How to combine the functions of having the ability to select multiple items from a drop down list and hide columns based on values of said list?

R

rgree05

New Member
Joined
Nov 1, 2023
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I have a list that is based on the header names of columns and am able to hide all but the selected column in the list based on the current coding I have. However, this only works for one selection from the list at a time. I would like to be able to select multiple column titles from the list and all of the selected columns remain visible while the rest are hidden.

I attempted to combine code that would let me select multiple items from the list with the existing code that allows me to hide all but the selected column title, but that does not work. Please see below for the current code I'm using.

I also realized that my current ShowHideColumns work isn't going to work because the list would need to have it "contain" and not "equal" a certain value and the current code has it hiding all but the column selected in the list. If two are on the list, then that code would contradict

VBA Code: 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
If Target.Cells.CountLarge > 1 Then Exit Sub 'only handling single-cell updates
    
HandleMultiSelect Target 'call the first action
    
ShowHideColumns Target '...then the next action
    
End Sub

Sub HandleMultiSelect(Target As Range)
'do your multi-select list handling here
Dim rngDropdown As Range
Dim oldValue As String
Dim newValue As String
Dim DelimiterType As String
DelimiterType = ", "
Dim DelimiterCount As Integer
Dim TargetType As Integer
Dim i As Integer
Dim arr() As String
 
If Destination.Count > 1 Then Exit Sub
On Error Resume Next
 
Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitError
 
If rngDropdown Is Nothing Then GoTo exitError
 
TargetType = 0
    TargetType = Destination.Validation.Type
If TargetType = 3 Then  ' is validation type is "list"
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 Or oldValue = newValue & Replace(DelimiterType, " ", "") Or oldValue = newValue & DelimiterType Then ' leave the value if there is only one in the list
oldValue = Replace(oldValue, DelimiterType, "")
oldValue = Replace(oldValue, Replace(DelimiterType, " ", ""), "")
                    Destination.Value = oldValue
ElseIf InStr(1, oldValue, DelimiterType & newValue) Or InStr(1, oldValue, newValue & DelimiterType) Or InStr(1, oldValue, DelimiterType & newValue & DelimiterType) Then
                    arr = Split(oldValue, DelimiterType)
If Not IsError(Application.Match(newValue, arr, 0)) = 0 Then
                    Destination.Value = oldValue & DelimiterType & newValue
 Else:
Destination.Value = ""
For i = 0 To UBound(arr)
If arr(i) <> newValue Then
                        Destination.Value = Destination.Value & arr(i) & DelimiterType
End If
 Next i
                Destination.Value = Left(Destination.Value, Len(Destination.Value) - Len(DelimiterType))
End If
ElseIf InStr(1, oldValue, newValue & Replace(DelimiterType, " ", "")) Then
oldValue = Replace(oldValue, newValue, "")
                    Destination.Value = oldValue
Else
                    Destination.Value = oldValue & DelimiterType & newValue
End If
Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", "") & Replace(DelimiterType, " ", ""), Replace(DelimiterType, " ", "")) ' remove extra commas and spaces
Destination.Value = Replace(Destination.Value, DelimiterType & Replace(DelimiterType, " ", ""), Replace(DelimiterType, " ", ""))
If Destination.Value <> "" Then
If Right(Destination.Value, 2) = DelimiterType Then  ' remove delimiter at the end
Destination.Value = Left(Destination.Value, Len(Destination.Value) - 2)
End If
End If
If InStr(1, Destination.Value, DelimiterType) = 1 Then ' remove delimiter as first characters
Destination.Value = Replace(Destination.Value, DelimiterType, "", 1, 1)
End If
If InStr(1, Destination.Value, Replace(DelimiterType, " ", "")) = 1 Then
Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", ""), "", 1, 1)
End If
DelimiterCount = 0
For i = 1 To Len(Destination.Value)
If InStr(i, Destination.Value, Replace(DelimiterType, " ", "")) Then
DelimiterCount = DelimiterCount + 1
End If
 Next i
If DelimiterCount = 1 Then ' remove delimiter if last character
Destination.Value = Replace(Destination.Value, DelimiterType, "")
Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", ""), "")
End If
End If
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
 
exitError:
Application.EnableEvents = True
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
End Sub

Sub ShowHideColumns(Target As Range)
If Target.Column = 3 And Target.Row = 8 Then
If Target.Value = [D10].Value Then
Application.Columns("F:CY").Hidden = True
Application.Columns("D:E").Hidden = False
            
ElseIf Target.Value = [F10].Value Then
Application.Columns("D:E").Hidden = True
Application.Columns("H:CY").Hidden = True
Application.Columns("F:G").Hidden = False
            
ElseIf Target.Value = [H10].Value Then
Application.Columns("D:G").Hidden = True
Application.Columns("J:CY").Hidden = True
Application.Columns("H:I").Hidden = False
            
ElseIf Target.Value = [J10].Value Then
Application.Columns("D:I").Hidden = True
Application.Columns("L:CY").Hidden = True
Application.Columns("J:K").Hidden = False
            
ElseIf Target.Value = [L10].Value Then
Application.Columns("D:K").Hidden = True
Application.Columns("N:CY").Hidden = True
Application.Columns("L:M").Hidden = False
            
ElseIf Target.Value = [N10].Value Then
Application.Columns("D:M").Hidden = True
Application.Columns("P:CY").Hidden = True
Application.Columns("N:O").Hidden = False
            
ElseIf Target.Value = [P10].Value Then
Application.Columns("D:O").Hidden = True
Application.Columns("R:CY").Hidden = True
Application.Columns("P:Q").Hidden = False
            
ElseIf Target.Value = [R10].Value Then
Application.Columns("D:Q").Hidden = True
Application.Columns("T:CY").Hidden = True
Application.Columns("R:S").Hidden = False
                  
ElseIf Target.Value = [T10].Value Then
Application.Columns("D:S").Hidden = True
Application.Columns("V:CY").Hidden = True
Application.Columns("T:U").Hidden = False
            
ElseIf Target.Value = [V10].Value Then
Application.Columns("D:U").Hidden = True
Application.Columns("X:CY").Hidden = True
Application.Columns("V:W").Hidden = False
            
ElseIf Target.Value = [X10].Value Then
Application.Columns("D:W").Hidden = True
Application.Columns("Z:CY").Hidden = True
Application.Columns("X:Y").Hidden = False
            
ElseIf Target.Value = [Z10].Value Then
Application.Columns("D:Y").Hidden = True
Application.Columns("AB:CY").Hidden = True
Application.Columns("Z:AA").Hidden = False
            
ElseIf Target.Value = [AB10].Value Then
Application.Columns("D:AA").Hidden = True
Application.Columns("AD:CY").Hidden = True
Application.Columns("AB:AC").Hidden = False
            
ElseIf Target.Value = [AD10].Value Then
Application.Columns("D:AC").Hidden = True
Application.Columns("AF:CY").Hidden = True
Application.Columns("AD:AE").Hidden = False
            
ElseIf Target.Value = [AF10].Value Then
Application.Columns("D:AE").Hidden = True
Application.Columns("AH:CY").Hidden = True
Application.Columns("AF:AG").Hidden = False
            
ElseIf Target.Value = [AH10].Value Then
Application.Columns("D:AG").Hidden = True
Application.Columns("AJ:CY").Hidden = True
Application.Columns("AH:AI").Hidden = False
                  
ElseIf Target.Value = [AJ10].Value Then
Application.Columns("D:AI").Hidden = True
Application.Columns("AL:CY").Hidden = True
Application.Columns("AJ:AK").Hidden = False
            
ElseIf Target.Value = [AL10].Value Then
Application.Columns("D:AK").Hidden = True
Application.Columns("AN:CY").Hidden = True
Application.Columns("AL:AM").Hidden = False
            
ElseIf Target.Value = [AN10].Value Then
Application.Columns("D:AM").Hidden = True
Application.Columns("AP:CY").Hidden = True
Application.Columns("AN:AO").Hidden = False
            
ElseIf Target.Value = [AP10].Value Then
Application.Columns("D:AO").Hidden = True
Application.Columns("AR:CY").Hidden = True
Application.Columns("AP:AQ").Hidden = False
            
ElseIf Target.Value = [AR10].Value Then
Application.Columns("D:AQ").Hidden = True
Application.Columns("AT:CY").Hidden = True
Application.Columns("AR:AS").Hidden = False
            
ElseIf Target.Value = [AT10].Value Then
Application.Columns("D:AS").Hidden = True
Application.Columns("AV:CY").Hidden = True
Application.Columns("AT:AU").Hidden = False
            
ElseIf Target.Value = [AV10].Value Then
Application.Columns("D:AU").Hidden = True
Application.Columns("AX:CY").Hidden = True
Application.Columns("AV:AW").Hidden = False
            
ElseIf Target.Value = [AX10].Value Then
Application.Columns("D:AW").Hidden = True
Application.Columns("AZ:CY").Hidden = True
Application.Columns("AX:AY").Hidden = False
            
ElseIf Target.Value = [AZ10].Value Then
Application.Columns("D:AY").Hidden = True
Application.Columns("BB:CY").Hidden = True
Application.Columns("AZ:BA").Hidden = False
            
ElseIf Target.Value = [BB10].Value Then
Application.Columns("D:BA").Hidden = True
Application.Columns("BD:CY").Hidden = True
Application.Columns("BB:BC").Hidden = False
                  
ElseIf Target.Value = [BD10].Value Then
Application.Columns("D:BC").Hidden = True
Application.Columns("BF:CY").Hidden = True
Application.Columns("BD:BE").Hidden = False
            
ElseIf Target.Value = [BF10].Value Then
Application.Columns("D:BE").Hidden = True
Application.Columns("BH:CY").Hidden = True
Application.Columns("BF:BG").Hidden = False
            
ElseIf Target.Value = [BH10].Value Then
Application.Columns("D:BG").Hidden = True
Application.Columns("BJ:CY").Hidden = True
Application.Columns("BH:BI").Hidden = False
            
ElseIf Target.Value = [BJ10].Value Then
Application.Columns("D:BI").Hidden = True
Application.Columns("BL:CY").Hidden = True
Application.Columns("BJ:BK").Hidden = False
            
ElseIf Target.Value = [BL10].Value Then
Application.Columns("D:BK").Hidden = True
Application.Columns("BN:CY").Hidden = True
Application.Columns("BL:BM").Hidden = False
            
ElseIf Target.Value = [BN10].Value Then
Application.Columns("D:BM").Hidden = True
Application.Columns("BP:CY").Hidden = True
Application.Columns("BN:BO").Hidden = False
            
ElseIf Target.Value = [BP10].Value Then
Application.Columns("D:BO").Hidden = True
Application.Columns("BR:CY").Hidden = True
Application.Columns("BP:BQ").Hidden = False
            
ElseIf Target.Value = [BR10].Value Then
Application.Columns("D:BQ").Hidden = True
Application.Columns("BT:CY").Hidden = True
Application.Columns("BR:BS").Hidden = False
            
ElseIf Target.Value = [BT10].Value Then
Application.Columns("D:BS").Hidden = True
Application.Columns("BV:CY").Hidden = True
Application.Columns("BT:BU").Hidden = False
            
ElseIf Target.Value = [BV10].Value Then
Application.Columns("D:BU").Hidden = True
Application.Columns("BX:CY").Hidden = True
Application.Columns("BV:BW").Hidden = False
                  
ElseIf Target.Value = [BX10].Value Then
Application.Columns("D:BW").Hidden = True
Application.Columns("BZ:CY").Hidden = True
Application.Columns("BX:BY").Hidden = False
            
ElseIf Target.Value = [BZ10].Value Then
Application.Columns("D:BY").Hidden = True
Application.Columns("CB:CY").Hidden = True
Application.Columns("BZ:CA").Hidden = False
            
ElseIf Target.Value = [CB10].Value Then
Application.Columns("D:CA").Hidden = True
Application.Columns("CD:CY").Hidden = True
Application.Columns("CB:CC").Hidden = False
            
ElseIf Target.Value = [CD10].Value Then
Application.Columns("D:CC").Hidden = True
Application.Columns("CF:CY").Hidden = True
Application.Columns("CD:CE").Hidden = False
            
ElseIf Target.Value = [CF10].Value Then
Application.Columns("D:CE").Hidden = True
Application.Columns("CH:CY").Hidden = True
Application.Columns("CF:CG").Hidden = False
            
ElseIf Target.Value = [CH10].Value Then
Application.Columns("D:CG").Hidden = True
Application.Columns("CJ:CY").Hidden = True
Application.Columns("CH:CI").Hidden = False
            
ElseIf Target.Value = [CJ10].Value Then
Application.Columns("D:CI").Hidden = True
Application.Columns("CL:CY").Hidden = True
Application.Columns("CJ:CK").Hidden = False
            
ElseIf Target.Value = [CL10].Value Then
Application.Columns("D:CK").Hidden = True
Application.Columns("CN:CY").Hidden = True
Application.Columns("CL:CM").Hidden = False
            
ElseIf Target.Value = [CN10].Value Then
Application.Columns("D:CM").Hidden = True
Application.Columns("CP:CY").Hidden = True
Application.Columns("CN:CO").Hidden = False
            
ElseIf Target.Value = [CP10].Value Then
Application.Columns("D:CO").Hidden = True
Application.Columns("CR:CY").Hidden = True
Application.Columns("CP:CQ").Hidden = False
                  
ElseIf Target.Value = [CR10].Value Then
Application.Columns("D:CQ").Hidden = True
Application.Columns("CT:CY").Hidden = True
Application.Columns("CR:CS").Hidden = False
            
ElseIf Target.Value = [CT10].Value Then
Application.Columns("D:CS").Hidden = True
Application.Columns("CV:CY").Hidden = True
Application.Columns("CT:CU").Hidden = False
            
ElseIf Target.Value = [CV10].Value Then
Application.Columns("D:CU").Hidden = True
Application.Columns("CX:CY").Hidden = True
Application.Columns("CV:CW").Hidden = False
            
ElseIf Target.Value = [CX10].Value Then
Application.Columns("D:CW").Hidden = True
Application.Columns("CX:CY").Hidden = False
              
ElseIf Target.Value = "SHOW ALL" Then
Application.Columns("ZZ").Hidden = True
Application.Columns("D:CY").Hidden = False
        
End If
End If

End Sub
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
You must log in or register to reply here.

Similar threads

E
  • Question Question
Can you combine these 2 macros ?
Replies
6
Views
186
excel4us
E
welshraz
  • Question Question
VBA - Restrict Code to One Column for Multi-choice Dropdown
Replies
2
Views
70
welshraz
welshraz
M
  • Question Question
Help making my VBA code apply to only one column
Replies
2
Views
538
Alex Blakenburg
A
J
  • Question Question
Multiple select validation list VBA
Replies
1
Views
212
jkpieterse
jkpieterse
K
  • Solved
Combining 3 Worksheet_Change events
Replies
4
Views
527
Kevo
K

Forum statistics

Threads
1,223,705
Messages
6,173,996
Members
452,542
Latest member
Bricklin

Share this page

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