Worksheet Event Excel VBA

Rayne
Rayne used Ask the Experts™
on
Hello,

I have a worksheet change event code I need to make. if users enters "=Sum(2+3") in a cell, it works fine. But if uswers enter something like this, in a cell
"=Su(1+2)"
it leads to "#Name?" in cell.

How do i capture this error in VBA code?

Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
how to capture that formula error in vba worksheet change code?

thank you
Commented:
Try this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 
If Application.WorksheetFunction.IsErr(Target) Then
    Debug.Print "There is an error"
End If

End Sub

Open in new window


[Edit]
You can replace the debug.print statement with whatever you want to do to capture (e.g., show a message box, enter a value in another cell).
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Or you may try this....

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If IsError(Target) Then
   MsgBox "The cell content produced an error.", vbExclamation, "Error!"
   Target.Interior.ColorIndex = 3
Else
   Target.Interior.ColorIndex = xlNone
End If
End Sub

Open in new window

Author

Commented:
thank you all :)

Commented:
Happy to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial