Combining VBA Codes

[JackReacher85]

All,

I have two lots of VBA that i need to have combined, my goal is to have a multi select drop down list applied only to cells in Column M ( first code) and add the date in to column O when all cells from Col A to M are completed (second code)

This is the multi select drop down code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    Dim selectedValue As String
    Dim oldValue As String

    ' Check if the change is within column M
    If Not Intersect(Target, Me.Range("M:M")) Is Nothing Then
        Application.EnableEvents = False
        For Each cell In Target
            If Not IsEmpty(cell.Value) Then
                ' If the cell already contains data, append the new value
                oldValue = cell.OldValue
                selectedValue = cell.Value
                If oldValue = "" Then
                    cell.Value = selectedValue
                Else
                    ' Avoid duplication
                    If InStr(1, oldValue, selectedValue, vbTextCompare) = 0 Then
                        cell.Value = oldValue & ", " & selectedValue
                    End If
                End If
            End If
        Next cell
        Application.EnableEvents = True
    End If
End Sub

This is the date addition code.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rowNum As Long
    Dim lastCol As Long
    Dim cell As Range
    Dim isComplete As Boolean

    ' Define the columns to check
    lastCol = 13 ' Column M

    ' Check if the change affects columns A to M
    If Not Intersect(Target, Me.Range("A:M")) Is Nothing Then
        Application.EnableEvents = False
        For Each cell In Target
            rowNum = cell.Row
            If rowNum > 1 Then ' Skip header row if any
                isComplete = True
                For i = 1 To lastCol
                    If IsEmpty(Me.Cells(rowNum, i)) Then
                        isComplete = False
                        Exit For
                    End If
                Next i
                
                ' If complete, add date to column O
                If isComplete Then
                    Me.Cells(rowNum, 15).Value = Date
                Else
                    ' Optionally clear date if not all cells are complete
                    Me.Cells(rowNum, 15).ClearContents
                End If
            End If
        Next cell
        Application.EnableEvents = True
    End If
End Sub

Ive racked my brain over this and cant seem to figure it out, any help is greatly appreciated.

 

[DanteAmor]

oldValue = cell.OldValue

That line in your first code doesn't work for me, but if it works for you then it's correct. In fact, I'm not going to touch the first code, I'm just going to add the second code.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    Dim selectedValue As String
    Dim oldValue As String

    ' Check if the change is within column M
    If Not Intersect(Target, Me.Range("M:M")) Is Nothing Then
        Application.EnableEvents = False
        For Each cell In Target
            If Not IsEmpty(cell.Value) Then
                ' If the cell already contains data, append the new value
                'oldValue = cell.oldValue
                oldValue = cell.Value
                selectedValue = cell.Value
                If oldValue = "" Then
                    cell.Value = selectedValue
                Else
                    ' Avoid duplication
                    If InStr(1, oldValue, selectedValue, vbTextCompare) = 0 Then
                        cell.Value = oldValue & ", " & selectedValue
                    End If
                End If
            End If
        Next cell
        Application.EnableEvents = True
    End If
    
    
    'add the date in to column O when all cells from Col A to M are completed
    Dim rowNum As Long
    Dim lastCol As Long, i As Long
    Dim isComplete As Boolean

    ' Define the columns to check
    lastCol = 13 ' Column M

    ' Check if the change affects columns A to M
    If Not Intersect(Target, Me.Range("A:M")) Is Nothing Then
        Application.EnableEvents = False
        For Each cell In Target
            rowNum = cell.Row
            If rowNum > 1 Then ' Skip header row if any
                isComplete = True
                For i = 1 To lastCol
                    If IsEmpty(Me.Cells(rowNum, i)) Then
                        isComplete = False
                        Exit For
                    End If
                Next i
                
                ' If complete, add date to column O
                If isComplete Then
                    Me.Cells(rowNum, 15).Value = Date
                Else
                    ' Optionally clear date if not all cells are complete
                    Me.Cells(rowNum, 15).ClearContents
                End If
            End If
        Next cell
        Application.EnableEvents = True
    End If
End Sub

----- --
Try the combined code and let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[JackReacher85]

Hey Dante,

Im afraid this code only works for adding the date in it doesnt work with the multi-select drop down function only being applied to the cells in Column M.

Appreciate any other suggestions you may have
Thanks in advance

 

[DanteAmor]

This is the multi select drop down code

Does that code work for you?

Or do you also want it to be fixed?

 

[DanteAmor]

doesnt work with the multi-select drop down function

As I told you in post #2, I did not modify your code and I warned you that the code does not work.
Now you can comment if the code worked for you.

This property does not exist: cell.OldValue, Or do you have more code in your sheet that you didn't put in your original post?