Worksheet change sub procedure

[Grant]

Hi there,

The following sub procedure:
Private Sub Worksheet_Change(ByVal Target As Range)

Target does not appear to be a Range object. I can't use
it like Target.value or iMyInteger = RcCell Is Target or
anything that would require an Object type.

Any ideas?

Grant.

 

[Chip Pearson]

Grant,

The Target argument is indeed a range reference. What exactly do
you mean when you say you can't "use it"?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Grant]

Type mismatch error for msgbox Target.value

-----Original Message-----
Grant,

The Target argument is indeed a range reference. What exactly do
you mean when you say you can't "use it"?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





.

 

[Norman Jones]

Hi Grant,

I could reproduce your situation with an unintentional error entry (e.g.
=25/0) or an intentional error entry such as =NA().

In these special situations what value can the sub return other than a
run-time error?

The following should demonstrate Target.Value does return the value of the
Target range object if there is a legitimate value to return and does return
the address of the Target range irrespective of the Target entry. This is
consistent with Target being a range object.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsError(Target.Value) Then
MsgBox Target.Value
End If
MsgBox Target.Address
End Sub

At the risk of overkill, Activecell is clearly a range object but try this
and see what happens:

Sub Tester()
ActiveCell.Value = 25 / 0
MsgBox ActiveCell.Value
End Sub

 

[Tom Ogilvy]

Try

Msgbox Target(1).Value

 

[Norman Jones]

Hi Tom,

A much more likely scenario!

Thank you.