Runtime Error 1004: Clearing cell next to dropdown list

Question

I'm trying to clear the cell next to my dropdownlist. The goal is to clear the cell next to it when it changes. My code works well if the excel sheet is simple and not too much other data is on there. Now on the sheet I want to have it implemented it always gives me error VBA Runtime Error 1004 “Application-defined or Object-defined error”. How can I solve this? Another thing that makes it harder is that I have also other code in the Private Sub Worksheet_Change(ByVal Target As Range)

I think a solution could be to find a way to get it to work without using Target. Unfortunately I don't seem to find something that works without this.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
    If Target.Column = 2 And Target.Validation.Type = 3 Then
        Application.EnableEvents = False
        Target.Offset(0, 1).ClearContents
    End If
End Sub

Edit: part of the other code

With Sheet2
        Select Case .Range("B2") 
            Case "C-shuttle 150 core"        
                .Range("F4:Q4").Locked = True
                .Range("B2").Locked = False

            Case "C-shuttle 250 core"
                .Range("F4:I4").Locked = False
                .Range("B2").Locked = False
                .Range("J4:Q4").Locked = True

            Case "C-shuttle 250 core with platfrom for DB or TD"
                .Range("B4:Q4").Locked = False
                .Range("B2").Locked = False
        
            Case "C-shuttle 350 core"
                .Range("B4:Q4").Locked = False
                .Range("B2").Locked = False        
        End Select
    End With

ExitHandler:
    Sheet2.Protect Password:="Secret"      ' ALWAYS PROTECT SHEET AT END ERROR OR NOT
    Application.EnableEvents = True
    Exit Sub

ErrHandler:
    MsgBox Err.Number & " - " & Err.Description, vbCritical
    Resume ExitHandler

Thanks a lot for your help!

What cell is the dropdown in? You should use `Intersect` with `Target` and that specific cell, to test if the cell that changed is the one that has the dropdown. — BigBen, Feb 26 '21 at 13:56
@BigBen multiple cells are dropdown lists, but not all the cells in tha column — Josse_, Feb 26 '21 at 15:08
`Application.EnableEvents = True` at the beginning of the code snippet does nothing and can be easily removed. — BigBen, Feb 26 '21 at 15:10

score 4 · Accepted Answer · answered Feb 26 '21 at 14:48

You will see the error 1004 if target has no validation set. Note that VBA will evaluate both parts of your If-statement. Even if Target.Column is not 2 (and therefore the whole IF cannot be True), VBA will check Target.Validation.Type.

Another problem is that target can contain more that one cell (if for example the user cut&paste data into more that one cell). You need to check every single cell separately.

Unfortunately, there is no easy test in VBA if a cell has validation, you need to use the On Error Resume Next to check for the type (see https://stackoverflow.com/a/31346246/7599798)

Furthermore, you have a problem with EnableEvents. Events need to be disabled if your trigger is about to modify data. This is to prevent that this modification itself calls the Change-Trigger, which can result in an endless recursive call (that eventually results in an Stack Overflow error). However, you need to Enable these events in any case, else the trigger will never be called again.

Your trigger could look like

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    
    Dim cell As Range
    For Each cell In Target
        If cell.Column = 2 Then
            Dim vType
            vType = Null
            On Error Resume Next
            vType = cell.Validation.Type
            On Error GoTo 0
    
            If vType = 3 Then
                cell.Offset(0, 1).ClearContents
            End If
        End If
    Next
    
    Application.EnableEvents = True
End Sub

I'll test it a bit and mark it as answered when I find this works perfectly. already thanks! — Josse_, Feb 26 '21 at 15:10

Runtime Error 1004: Clearing cell next to dropdown list

1 Answers1