Debugging and Improving VBA Code for Worksheet Change Event in Excel

Question

I have a VBA code in an Excel worksheet that responds to changes in specific cells. The code is supposed to handle changes in cells with data validation and should concatenate values if multiple items from the dropdown are selected. However, I'm having issues with it as it is recurrently giving me an "Object Required" error (runtime error 424). I am looking for further assistance in debugging and optimizing this code.

Here's the VBA code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngDropdown As Range
    Dim oldValue As String
    Dim newValue As String
    Dim DelimiterType As String
    Dim DelimiterCount As Integer
    Dim TargetType As Integer
    Dim i As Integer
    Dim arr() As String

    DelimiterType = ", "

    ' New code: Check if the changed cell is in column AP and its value is "On Assignment"
    If Target.CountLarge = 1 Then
        If Target.Column = 42 And Target.Value = "On Assignment" Then
            CopyOnAssignment Target.Row
        End If
    End If

    If Target.Count > 1 Then Exit Sub

    ' Check if the changed cell is in column L
    If Target.Column <> 12 Then Exit Sub 'Column L is the 12th column

    ' Attempt to set rngDropdown to cells with validation
    On Error Resume Next
    Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo exitError

    If rngDropdown Is Nothing Then GoTo exitError

    ' Check if Target has validation before accessing its Type
    On Error Resume Next
    TargetType = Target.Validation.Type
    On Error GoTo exitError

    If TargetType = 3 Then ' if validation type is "list"
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        newValue = Target.Value
        Application.Undo
        oldValue = Target.Value
        Target.Value = newValue
        
        ' ... [rest of your code remains unchanged]

        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If

exitError:
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' Your SelectionChange subroutine code (currently empty)
End Sub

The code should do the following:

If a cell in column AP is changed to "On Assignment", call the CopyOnAssignment function with the row number. If a cell in column L with data validation is changed, concatenate the new value with the existing values, separated by a comma. There are several conditions and string manipulations to ensure values aren't repeated or added incorrectly.

Issues I'm facing:

on debugging following line of code is being highlighted:

If Target.Count > 1 Then Exit Sub

I have tried changing it but still getting the error. I feel there might be a more efficient or cleaner way to do this. Any assistance in debugging and refactoring the code would be much appreciated!

Answer 1

If you're getting an "Object required" error when trying to reference Target, it's possible that some of your code (maybe the call to CopyOnAssignment Target.Row) has deleted the range referenced by Target.

So if you then try to later reference Target in the Change event handler, you get an error.

Suggested fix

    If Target.CountLarge > 1 Then Exit Sub 'Do this first: no need for 
                                           '  any counting after this...
    '...
    '...
    If Target.Column = 42 And Target.Value = "On Assignment" Then
        CopyOnAssignment Target.Row
        Exit Sub '<<< nothing else to do in this case...
    End If