Merging 2 VBA Codes

[crusader0011]

Hi Guys,

I'm new to VBA coding, so please bare with me

I want to implement multiple selections within several drop down list, after googling and searching here and there i managed to find the following coding lines which work great in any given column:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long
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 = 9 Then
    If oldVal = "" Then
      'do nothing
      Else
      If newVal = "" Then
      'do nothing
      Else
        lUsed = InStr(1, oldVal, newVal)
        If lUsed > 0 Then
            If Right(oldVal, Len(newVal)) = newVal Then
                Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)
            Else
                Target.Value = Replace(oldVal, newVal & ", ", "")
            End If
        Else
            Target.Value = oldVal _
              & ", " & newVal
        End If
        
      End If
    End If
  End If
End If

exitHandler:
  Application.EnableEvents = True
End Sub

Now, The issue is that I want the multiple selection to apply to at least 2 Columns, I've tried several combinations wit If/End if/Else & Else If, and after cleaning up I seem to disable the loop effect,

Can anyway help?

kindest regards!

Cruz

[crusader0011]

Hi Guys, Can anyone help?

[abousetta]

I can try to look at it later, but I'm not sure what you are trying to do. Could you elaborate in words and post a sample workbook? You'll tend to get more responses if you take both actions.

Good luck.

abousetta

[crusader0011]

Thanks abousetta,

the following Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long
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 = 9 Then
    If oldVal = "" Then
      'do nothing
      Else
      If newVal = "" Then
      'do nothing
      Else
        lUsed = InStr(1, oldVal, newVal)
        If lUsed > 0 Then
            If Right(oldVal, Len(newVal)) = newVal Then
                Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)
            Else
                Target.Value = Replace(oldVal, newVal & ", ", "")
            End If
        Else
            Target.Value = oldVal _
              & ", " & newVal
        End If
        
      End If
    End If
  End If
End If

exitHandler:
  Application.EnableEvents = True
End Sub

enables me to apply mutliple selections out of a Drop Down List but only in Column '9', now what I need is to apply the macro to Column 8 & 9 at once, I tried to replicate the code inside the code using IF and END IF but somehow it always ends up applying the macro only to one Column,

thank you for your help

[crusader0011]

Can someone help please?

[crusader0011]

+1
Can anyway assist me?

[abousetta]

Hi,

I can't test at the moment, but replace this line

  If Target.Column = 9 Then

with

  If Target.Column = 8 or Target.Column = 9 Then

abousetta

[crusader0011]

Work Great, Thanks!

[abousetta]

Thanks for the feedback.

Good luck.

abousetta