VBA event throws error when pressing a button?

When i click a button on my worksheet the below event is called.

I get the error 'Type mismatch'

I suspect I need another if statement to stop the original IF being evaluated if the event is due to a button being pressed?

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target = Range("D4") Then   'Error is here

    End If

End Sub

Answers


This

If Target = Range("D4") Then

is equivalent to this

If Target.Value = Range("D4").Value Then

which clearly is not what you want. You will probably get the error you describe if Target.Value happens not to be of the same type as Range("D4").Value.

What you want is this:

If Not Intersect(Target, Range("D4")) Is Nothing Then

EDIT I just managed to reproduce your error. It occurs if the Target range is of a different size than Range("D4") i.e. spans more than one cell. As @Dick Kusleiska notes, it also occurs if one of the two is an error value. Maybe it's triggered by other things as well, I don't know. Anyhow, the point is, your If condition is wrong!


Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa
    Application.EnableEvents = False

    If Not Intersect(Target, Range("D4")) Is Nothing Then

    End If

LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

EDIT:

I would also recommend using Error handling and setting the .EnableEvents to false to avoid and possible endless loops :)


Need Your Help

Is there a way to download Google Play Services SDK R15?

android google-cast chromecast

Is the Google Play Services SDK R15 available to download anywhere? The "ChromeCast SDK" was "released" Monday, according to hundreds of news sites. Those same news sites claim Android app develope...

Date format in models

django-models

Hello how do i change the date format in models