Excel VBA Target.Address being modified and causing Error 13 type mismatch

Question

Prototypical post: New to VBA, unable to resolve an issue after having read multiple posts/websites, and now turning to all the fantastic people here who's posts have gotten me this far.

I have a worksheet with data validation in column C (list; forced-choice Yes/No options). If user selects "No" in C7, then C9:C11 need to automatically and immediately populate as "No." I have gotten this to work by the following:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$C$7" And Target.Value = "No" Then
    Range("$C$9").Value = "No"
    Range("$C$10").Value = "No"
    Range("$C$11").Value = "No"
    End If
End Sub

I also have a text box on the same worksheet (i.e., Sheet5) that when clicked fires a macro which clears the contents of C6:C7. This Reset macro is in a module under General.

Sub C_B_Reset()
Sheet5.Range("C6:C7").ClearContents
End Sub

Individually, these work fine, but when both exist it results in Type 13 error at the Target.Address after the Reset macro is fired. After firing the Reset macro, the "If Target.Address" portion resolves to the range referenced in the Reset macro (i.e., C6:C7). Because "If Target.Address" expects a single, absolute cell reference (e.g., $C$7), it is throwing the mismatch code because it instead is resolving to (C6:C7) when the mouse is hovered over it.

Even if the Reset macro is completely deleted, the same issue happens if the following is used in the Target.Address code:

Range("$C$9:$C$11").Value = "No"

The Target.Address then resolves to "$C$9:$C$11" and throws the Type 13 mismatch error.

It appears that if "Range" is used to refer to a range of cells in any other macro, it automatically gets assigned as Target.Address. However, this doesn't happen if Range only refers to single cells (which is why there are separate lines for C9 to C11 in the Worksheet_Change code).

I'm sure I'm using incorrect terminology, but I hope I explained it well enough, because I sure would appreciate some help.

Thanks for taking a look,

Disable events before running the reset macro. Then turn back on afterwards. Or you could add a restriction on your change event to exit if target.count>1. — SJR
Instead of If Target.Address = "$C$7 use If Intersect(Target, Range("C7") — urdearboy

BigBen BigBen · Accepted Answer · 2020-05-15T16:55:29

"Excel VBA Target.Address being modified and causing Error 13 type mismatch"

Target.Address isn't the problem here... Target is the cell(s) that were changed, so Target.Address will be $C$6:$C$7 when you clear both C6 and C7.

The main problem is this:

... And Target.Value = "No" ...

This will fail with a Type Mismatch error when Target is a multi-cell range, because then Target.Value is a 2D Variant array, which you can't compare to "No".

Also, the normal approach is to use Intersect instead of considering Target.Address.

If you're only concerned about C7, then perhaps write like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.Range("C7")) Is Nothing Then
       If Me.Range("C7").Value = "No" Then
            On Error GoTo SafeExit
            Application.EnableEvents = False ' Avoid re-triggering the event
            Me.Range("C9:C11").Value = "No"
       End If
    End If

SafeExit:
    Application.EnableEvents = True
End Sub

Excel VBA Target.Address being modified and causing Error 13 type mismatch

2 Answers