How can I use multiple VBA codes in one worksheet

[Shadowlightgirl]

Hello,

I'm REALLY new to this so I'm having trouble deciphering the answers to this question from other users scenarios. For transparency, the code I'm using is from many web searches until I found different codes that do what I want. I have no idea how to write them from scratch. So please be gentle with me.

I would like to be able to do multiple automatic processes on one worksheet but can't figure out how to do this. I can get each of the VBA codes to work on their own, but no idea how to do more than one at a time.

The two I want to use have different purposes which I'll explain below with the code I've got for each. If it's possible to do both and someone could explain how to do it, I'd sure appreciate it. Thank you.

1) Allow multiple selections from drop down list in column K

Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from Online Excel Tips & Tutorials
' 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.Column = 11 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 & ", " & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

2) Automatically update the date in column Y if any data in corresponding row is changed

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Cells(Target.Row, "Y") = Date
End Sub

 

[Colo]

Welcome to the board!
To make it work, on a worksheet module, you can place only one Worksheet_Change event. Seems the procedure putting Date in Column Y works when only 1 cell has been changed, if this condition is okay for another procedure, you can combine those two as follows. Quite simple.

Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from Online Excel Tips & Tutorials
' To allow multiple selections in a Drop Down List in Excel (without repetition)
    Dim Oldvalue As String
    Dim Newvalue As String
    Application.EnableEvents = False    'I changed here as False from True
    On Error GoTo Exitsub

    If Target.Cells.Count > 1 Then Exit Sub
    Cells(Target.Row, "Y") = Date

    If Target.Column = 11 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 & ", " & Newvalue
                Else:
                    Target.Value = Oldvalue
                End If
            End If
        End If
    End If
    '    Application.EnableEvents = True 'I comment out here because it's not necessary
Exitsub:
    Application.EnableEvents = True
End Sub

 

[Shadowlightgirl]

Welcome to the board!
To make it work, on a worksheet module, you can place only one Worksheet_Change event. Seems the procedure putting Date in Column Y works when only 1 cell has been changed, if this condition is okay for another procedure, you can combine those two as follows. Quite simple.

Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from Online Excel Tips & Tutorials
' To allow multiple selections in a Drop Down List in Excel (without repetition)
    Dim Oldvalue As String
    Dim Newvalue As String
    Application.EnableEvents = False    'I changed here as False from True
    On Error GoTo Exitsub

    If Target.Cells.Count > 1 Then Exit Sub
    Cells(Target.Row, "Y") = Date

    If Target.Column = 11 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 & ", " & Newvalue
                Else:
                    Target.Value = Oldvalue
                End If
            End If
        End If
    End If
    '    Application.EnableEvents = True 'I comment out here because it's not necessary
Exitsub:
    Application.EnableEvents = True
End Sub

Hi Colo,

Thank you. I tried this but it only allows the date to change with the rows. The functionality for choosing multiple selections from the drop down list in column K doesn't work.

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("A:X")) Is Nothing Then Exit Sub
    Dim Oldvalue As String, Newvalue As String
    Application.EnableEvents = False
    Select Case Target.Column
        Case Is = 11
            Newvalue = Target.Value
            Application.Undo
            Oldvalue = Target.Value
            If Oldvalue = "" Then
              Target.Value = Newvalue
            ElseIf InStr(1, Oldvalue, Newvalue) = 0 Then
              Target.Value = Oldvalue & ", " & Newvalue
            Else
              Target.Value = Oldvalue
            End If
            Range("Y" & Target.Row) = Date
        Case Else
            Range("Y" & Target.Row) = Date
    End Select
    Application.EnableEvents = True
End Sub

 

[Shadowlightgirl]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("A:X")) Is Nothing Then Exit Sub
    Dim Oldvalue As String, Newvalue As String
    Application.EnableEvents = False
    Select Case Target.Column
        Case Is = 11
            Newvalue = Target.Value
            Application.Undo
            Oldvalue = Target.Value
            If Oldvalue = "" Then
              Target.Value = Newvalue
            ElseIf InStr(1, Oldvalue, Newvalue) = 0 Then
              Target.Value = Oldvalue & ", " & Newvalue
            Else
              Target.Value = Oldvalue
            End If
            Range("Y" & Target.Row) = Date
        Case Else
            Range("Y" & Target.Row) = Date
    End Select
    Application.EnableEvents = True
End Sub

That did it! Thank you so much.

 

[mumps]

You are very welcome.