Hi all, I'm new to the forum so apologies beforehand for any errors while posting this.
I'm also very new to VBA coding, but looked around and eventually put together the below code from various sources, with some flaws though. My requirements with the coding are:
1) Select multiple items within an Excel validation (drop-down) list
2) There are several validation lists on my worksheet but I want to allow multiple selection for one column only. Hence I've added the line If Target.Column = 16 Then , where '16' is the column number I'm applying the code to.
3) Separate multiple items within a cell using commas
4) Don't allow duplicates within a cell e.g. if the list is a range of colours, users should be able to select as many colours from the list as they like, but never select the same colour twice. Note that this limitation is on the cell level only. When user proceeds to the next cell, they can again select any/all colours
5) Allow cell content to be erased / cells to be left blank.
Bonus: Show an error message if user selects a duplicate item within a cell
Good news: With the code below I've been able to achieve points 1-4
Bad news: I'm not being able to leave the cells blank or erase content.
(To confirm, when creating the data validation list, I have made sure to tick the 'Ignore Blank Cell' check box).
Is anyone able to help me incorporate point 5 into the below code please?
If I can also get an error message to pop up, that's a bonus. But I can live without it!
Many thanks!
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 16 Then
If InStr(1, oldVal, newVal, 1) > 0 Then
Target.Value = oldVal 'reinsert old value, user selected same value twice
Else
If oldVal = "" Then
Else
If newVal = "" Then
Else
Target.Value = oldVal & ", " & newVal
End If
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
I'm also very new to VBA coding, but looked around and eventually put together the below code from various sources, with some flaws though. My requirements with the coding are:
1) Select multiple items within an Excel validation (drop-down) list
2) There are several validation lists on my worksheet but I want to allow multiple selection for one column only. Hence I've added the line If Target.Column = 16 Then , where '16' is the column number I'm applying the code to.
3) Separate multiple items within a cell using commas
4) Don't allow duplicates within a cell e.g. if the list is a range of colours, users should be able to select as many colours from the list as they like, but never select the same colour twice. Note that this limitation is on the cell level only. When user proceeds to the next cell, they can again select any/all colours
5) Allow cell content to be erased / cells to be left blank.
Bonus: Show an error message if user selects a duplicate item within a cell
Good news: With the code below I've been able to achieve points 1-4Bad news: I'm not being able to leave the cells blank or erase content. (To confirm, when creating the data validation list, I have made sure to tick the 'Ignore Blank Cell' check box).
Is anyone able to help me incorporate point 5 into the below code please?
If I can also get an error message to pop up, that's a bonus. But I can live without it!
Many thanks!
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 16 Then
If InStr(1, oldVal, newVal, 1) > 0 Then
Target.Value = oldVal 'reinsert old value, user selected same value twice
Else
If oldVal = "" Then
Else
If newVal = "" Then
Else
Target.Value = oldVal & ", " & newVal
End If
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
Last edited:
