Gord,

You can only test the Target object if it is an argument of the event. It is
an argument to Change, and to SelectionChange, but Calculate has no
arguments at all (when a cell is changed Calculate works out which cells to
re-evaluate based upon a complex internal Excel algorithm, using precedents,
descendants, volatile functions etc.; so it doesn't need to be told what to
work on, which is effectively what the argument(s) is(are)).

In the code you show, there is no Target, you could specify it, but it would
be meaningless. That is what I meant by 'Just do it ... ', as you have no
idea what caused Calculate to be triggered, you have to Just do it, each
time, every time.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Yes.....that works but this doesn't

Private Sub Worksheet_Calculate()
Const WS_RANGE As String = "A1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = .Value
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub

If I REM out the On Error GoTo line, I throw an error on

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

Something here I just don't understand with my limited knowledge of
events.


Gord

On Sun, 3 Dec 2006 01:06:48 -0000, "Bob Phillips"
wrote:

Hi Gord,

Just do it in Calculate.

Private Sub Worksheet_Calculate()
Me.Name = Range("A1").Value
End Sub

Of course it does mean it happens for every formula/value change that
will
trigger a calculate.

Gord Dibben MS Excel MVP