Capture error of value

[tiredofit]

This code will display the value if ONE cell is changed:

Private Sub Worksheet_Change(ByVal Target As Range)

    Debug.Print Target.Value
   
End Sub

If multiple cells are selected and a value is entered by pressing SHIFT + ENTER, the above code returns an error, as expected.

I want to capture that error like this:

If IsError(Target.Value) Then .....

but it seems it never to hit the error part, even though if I typed:

Target.Value

in the Immediate Window, it does show an error message:

Run-time error '91'

Object variable or With block variable not set

How can I capture such an error?

Thanks

 

[Joe4]

This is how you can loop through a multi-cell range triggered by this code to get the value of each cell:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
   
    For Each cell In Target
        Debug.Print cell.Value
    Next cell
  
End Sub

So if you want to check for errors, add that code before the For/Next lines, referencing "cell", not "Target".

 

[tiredofit]

This is how you can loop through a multi-cell range triggered by this code to get the value of each cell:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
  
    For Each cell In Target
        Debug.Print cell.Value
    Next cell
 
End Sub

So if you want to check for errors, add that code before the For/Next lines, referencing "cell", not "Target".

Thanks

 

[Joe4]

You are welcome.