Having multiple Worksheet_Change (ByVal Target As Range) working in the same worksheet

[AlexJones1]

Hi, I have managed to code my spreadsheet to allow multiple selections (with no duplicates) from different data lists using the data validation first and then writing the following VBA:

The big Target.Address bit in the middle just covers the cells that I want it to work on (it get it might be a bit messy but it's working). However, I am struggling to expand this so it can do it on another cell but instead of inserting a '|' I need it to insert a comma, I have tried repeating the code with the specific cell references with a new private sub but I was getting the ambiguous name error. The only bit I need to change/add in the above coding is different cell references and changing the pipe to a comma.

Thanks,
Alex

 

[Candyman8019]

I would suggest approaching it something like this:

Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(target, Range("M12,M22,M32,M42")) Is Nothing Then
    'do something here
End If

If Not Intersect(target, Range("Z10")) Is Nothing Then
    'do something here
End If

If Not Intersect(target, Range("F43")) Is Nothing Then
    'do something here
End If
End Sub

 

[rollis13]

Hi to all.
Supposing that the other cell is "$L$212" I would use this added code:

'[...]
If InStr(1, Oldvalue, Newvalue) = 0 Then
    If Target.Address = "$L$212" Then         '<- added
        Target.Value = Oldvalue & "," & Newvalue '<- added
    Else                                      '<- added
        Target.Value = Oldvalue & "|" & Newvalue
    End If
Else:
'[...]

 

[AlexJones1]

Thanks both for the suggestion. I managed to get it to work using rollis13's suggestion.
Cheers,
Alex

 

[rollis13]

Thanks for the positive feedback, glad we were able to help.