VBA RunTime Error 13: Type Mismatch Worksheet Change Event

[truebluewoman]

I have a range of cells that if the value is changed to "R", then it should clear the contents of the certain cells to their right. The code actually works, but then I get an error. "RunTime Error '13' Type Mismatch.

When I debug, it appears that the "offending" piece of the code is the third line which is the condition of whether or not the contents to the right should be cleared out. I read on a separate thread that "Target" is a range of cells, so using "Target" in my conditions is wrong. How do I write the condition so I do not get this error? Can anyone help me fix this code? Thank you!

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("ALL_BED_STATUS")) Is Nothing Then

    If Target = "R" Then
    
        ActiveSheet.Range(Target.Offset(, 4), Target.Offset(, 12)).ClearContents

    End If

End If

On Error GoTo ErrorHandler
ErrorHandler:

Exit Sub

End Sub

 

[rollis13]

You are going redundant when you use ClearContents since your macro starts all over again. A ClearContents is considered a change in your sheet so event Worksheet_Change triggers. Use:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("ALL_BED_STATUS")) Is Nothing Then
        If Target = "R" Then
            Application.EnableEvents = False      '<-added
            ActiveSheet.Range(Target.Offset(, 4), Target.Offset(, 12)).ClearContents
            Application.EnableEvents = True       '<-added
        End If
    End If
    On Error GoTo ErrorHandler
ErrorHandler:
    Exit Sub
End Sub

 

[truebluewoman]

You are going redundant when you ClearContents so your macro starts all over again, use:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("ALL_BED_STATUS")) Is Nothing Then
        If Target = "R" Then
            Application.EnableEvents = False      '<-added
            ActiveSheet.Range(Target.Offset(, 4), Target.Offset(, 12)).ClearContents
            Application.EnableEvents = True       '<-added
        End If
    End If
    On Error GoTo ErrorHandler
ErrorHandler:
    Exit Sub
End Sub

That worked perfectly! I tried the Application.EnableEvents earlier, but I put it in the wrong area of the code and it just turned off events and did nothing Thank you SO much!

 

[Trebor76]

Also the way you're using the error handler seems wrong to me. See how this goes:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo ErrorHandler

    If Intersect(Target, Range("ALL_BED_STATUS")) Is Nothing Then
        If StrConv(Target, vbUpperCase) = "R" Then
            Application.EnableEvents = False
                Range(Target.Offset(, 4), Target.Offset(, 12)).ClearContents
            Application.EnableEvents = True
        End If
    End If

Exit Sub

ErrorHandler:

    Application.EnableEvents = True 'Ensure macros are enabled in the event of an error with the above code

End Sub

Regards,

Robert

 

[rollis13]

Glad having been of some help.