Hi there! I'm trying to modify a bit of VBA code from Mr Trump Excel that allows the user to select more than one value from a drop down list. Each time you select a value, it adds a new one so you end up with many values in a single cell.
Currently, this code will run on any cells that are above Row 5 and above Column 6 ("If Target.Row > 5 And Target.Column > 6 Then...")
However, I don't want this, what I actually need is for the code to run ONLY on specific rows - only those rows that contain the text string "Including".
Is it possible to tweak this code so that it runs based on finding a string value?
An altrernative would be to specificy each individual row by its number, but there will be a lot so this would be a nightmare.
Thanks So much!
Alex
Currently, this code will run on any cells that are above Row 5 and above Column 6 ("If Target.Row > 5 And Target.Column > 6 Then...")
However, I don't want this, what I actually need is for the code to run ONLY on specific rows - only those rows that contain the text string "Including".
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Row > 5 And 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
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & vbNewLine & "• " & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
Is it possible to tweak this code so that it runs based on finding a string value?
An altrernative would be to specificy each individual row by its number, but there will be a lot so this would be a nightmare.
Thanks So much!
Alex