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!

`CountLarge` might be safer than `Count` - there are some cases where `Count` can overflow. OP already uses `CountLarge` in the first check, but not in the next one... — Tim Williams, Aug 11 '23 at 17:54
`If Target.CountLarge > 1 Then Exit Sub` should be the first line in the sub. No need to check the count after that. Note if `CopyOnAssignment` ends up (for example) *deleting* the range pointed to by `Target`, that may be why you're getting an error the next time you try to reference `Target` in the Change event handler. If that's the case you should `Exit Sub` after that call. — Tim Williams, Aug 11 '23 at 17:59
I tried both ```CountLarge``` as well as ```If Target.Cells.Count > 1 Then Exit Sub``` it is still giving error, now while debugging excel is highlighting these as error lines, i am not sure where am i going wrong — Manish Tripathi, Aug 11 '23 at 18:01
@TimWilliams i guess you are correct ```CopyOnAssignment``` seems to be the reason, this is occurring only when the row is getting deleted, could you suggest a way so that i can fix it — Manish Tripathi, Aug 11 '23 at 18:06
What does `CopyOnAssigment` do? Should you `Application.EnableEvents = False` before it? — BigBen, Aug 11 '23 at 18:38
@BigBen when ever in column AP the value is "On Assignment", ```CopyOnAssigment``` deletes that row and pastes the content of that row in a different worksheet — Manish Tripathi, Aug 11 '23 at 18:54
Deleting the row modifies the worksheet, causing the change event to fire again. — BigBen, Aug 11 '23 at 18:54
.... as suggested previously, `Application.EnableEvents = False`, then `Application.EnableEvents = True` afterwards. Safe practice would be to error handle there too with `On Error GoTo exitError`. — BigBen, Aug 11 '23 at 18:59

score 1 · Accepted Answer · answered Aug 11 '23 at 18:08

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

answered Aug 11 '23 at 18:08

Tim Williams

154,628
8
97
125

I tried this but still the result is same, when ever CopyOnAssignment runs deleting a row, the error appears – Manish Tripathi Aug 11 '23 at 18:35
How can following lines throw an error if you already called `Exit Sub` ? – Tim Williams Aug 11 '23 at 18:37
I made the following change however now the code is not running ```If Target.CountLarge > 1 Then Exit Sub ' New code: Check if the changed cell is in column AP and its value is "On Assignment" If Target.Column = 42 And Target.Value = "On Assignment" Then CopyOnAssignment Target.Row End If ' 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 ``` – Manish Tripathi Aug 11 '23 at 18:51
Please edit your question to add additional code - it's not useful as a comment. "Not running" means what exactly? Did you end up with events disabled and it doesn't even fire? Or something else. – Tim Williams Aug 11 '23 at 19:03
1

Looks like you did not add the `Exit Sub` directly after the call to `CopyOnAssignment` – Tim Williams Aug 13 '23 at 01:46

Debugging and Improving VBA Code for Worksheet Change Event in Excel

1 Answers1