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 https://trumpexcel.com ' 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 Application.Undo Oldvalue = Target.Value If Oldvalue = "" Then Target.Value = Newvalue Else Target.Value = Oldvalue & ", " & Newvalue End If End If End If Application.EnableEvents = True Exitsub: 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.
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