Using VBA to allow Select all that Apply in Excel in Multiple Columns on the same sheet

apologies if this is something that has been answered in the past, I attempted a search but could not find an exact solution for this.

I am creating an activity tracker for a colleague in Excel. To quickly summarize my issue, we’d like to be able to "Select all that apply" from drop down lists in two separate columns on the same sheet.

Currently I am using this bit of VBA code to enable this functionality for one column:

Private Sub Worksheet_Change(ByVal Target As Range)

'Code by Sumit Bansal from
' To make mutliple selections in a Drop Down List in Excel

Dim Oldvalue As String
Dim Newvalue As String

On Error GoTo Exitsub
If Target.Column = 6 Then
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
    Else: If Target.Value = "" Then GoTo Exitsub Else
        Application.EnableEvents = False
        Newvalue = Target.Value
        Oldvalue = Target.Value
        If Oldvalue = "" Then
            Target.Value = Newvalue
            Target.Value = Oldvalue & ", " & Newvalue
        End If
    End If
End If
Application.EnableEvents = True
Application.EnableEvents = True
End Sub

Now I’m not very experienced with VBA. I found this code online, and altered it very slightly to suit my needs (applying to all of Column 6 instead of one cell).

I would like this to work for 3 Columns on the sheet, Columns 1,6, and 9. I would imagine that this is achieved by adding an Else statement somewhere below my "If Target.Column = 6" statement, but I do not know where to plug this in.

Any help would be greatly appreciated. I’m working on understanding more of the actual syntax of VBA, but I currently have a very cursory understanding of the language so I’m at a bit of a loss here.

>Solution :

Trying to answer your question, you may change the folowing If statement:

If Target.Column = 6 Then


If Target.Column = 1 Or Target.Column = 6 Or Target.Column = 9 Then

Leave a Reply