Help With VBA target range instead of cell

[JohnBell79]

Hi,

I have managed to write a code to allow multiple selections in a cell from a drop down list. However It only works for 1 cell and not all the cells in that column. Whenever I try to apply a range to the code it stops working. Any tips appreciated.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim old_val As String
Dim new_val As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$C$2" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
new_val = Target.Value
Application.Undo
old_val = Target.Value
If old_val = "" Then
Target.Value = new_val
Else
If InStr(1, old_val, new_val) = 0 Then
Target.Value = old_val & vbNewLine & new_val
Else:
Target.Value = old_val
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

Thanks

 

[Joe4]

Welcome to the Board!

If you want to apply it to all of column C, excluding row 1 (the header), try changing this line in your code:

If Target.Address = "$C$2" Then

to this:

If (Target.Column = 3) And (Target.Row > 1) Then

 

[JohnBell79]

Thank you! That worked perfectly. I knew it would be simple I just hit a stumbling block

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[Joe4]

Note one other thing you may want to do before that line of code is add the following:

If Target.CountLarge > 1 Then Exit Sub

That ensures that the code runs only when a single cell is updated at a time, and avoid errors when you do things like delete a whole row (which updates a whole row of cells at once).

 

[JohnBell79]

You may be able to assist with one more thing! from an aesthetic POV. The code is working perfectly however it is automatically wrapping the text in the cell and making me have to then unwrap it. Wondering if I can get in just to put a "," in between each selection??

Like this???

 

[Joe4]

You may be able to assist with one more thing! from an aesthetic POV. The code is working perfectly however it is automatically wrapping the text in the cell and making me have to then unwrap it. Wondering if I can get in just to put a "," in between each selection??

View attachment 95548

Like this???

That is really a whole different question, and as such it would be best to post it to a new question, so everyone can see it as a new unanswered questions in the "Unanswered threads" listing that many people use to look for new, unanswered questions to help on.

 

[JohnBell79]

No Worries, thanks for the help so far