Geek Answers Handbook

Does a VBA event raise an error when a button is clicked?

When I click a button on my sheet, the event below is triggered.

I get an error "Type of mismatch"

I suspect that I need another if statement to stop the original IF if its event was triggered by a button click?

Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("D4") Then 'Error is here End If End Sub
+4
source share
2 answers

it

 If Target = Range("D4") Then

equivalent to this

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

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

What you want is:

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

EDIT I just managed to reproduce your error. This happens if the Target range has a different size than Range("D4") , that is, it spans more than one cell. As @Dick Kusleiska notes, this also happens if one of the two is an error value. Perhaps this is caused by other things, I do not know. In any case, the fact is that your If condition is incorrect!

+5
source

try it

 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 .EnableEvents to false to avoid possible endless loops :)

+3
source

More articles:


All Articles