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
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!
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
I would also recommend using Error handling and setting the .EnableEvents to false to avoid and possible endless loops :)