I want a given cell eg z100 to constantly - or at each recalculation - to
hold the contents - or reference - of ActiveCell (so a text box can highlight
it.). My code does it but only once at each run of the module, then it sticks
or throws an error.

address of active cell at calc: =CELL("address")

contents: =INDIRECT(CELL("address"))


"ChrisR" wrote in message
...
|I want a given cell eg z100 to constantly - or at each recalculation - to
| hold the contents - or reference - of ActiveCell (so a text box can
highlight
| it.). My code does it but only once at each run of the module, then it
sticks
| or throws an error.

And the code is..............?


Gord Dibben MS Excel MVP

On Thu, 24 Sep 2009 09:19:06 -0700, ChrisR
wrote:

I want a given cell eg z100 to constantly - or at each recalculation - to
hold the contents - or reference - of ActiveCell (so a text box can highlight
it.). My code does it but only once at each run of the module, then it sticks
or throws an error.

Homey suggested - I think -

Sub ActiveCell()
'
' ActiveCell Macro
' Macro recorded 28/09/2009 by Sheffield City Council
'

address of active cell at calc: =CELL("address")

contents: =INDIRECT(CELL("address"))

End Sub


But as a Macro this doesn't do anything except generate an error - "Compile
error Syntax error". I want it to paste the contents or reference of
ActiveCell into Z100, whenever I recalc (or when I run a Macro?)

Sorry to be naiive.

Chris

"Gord Dibben" wrote:

And the code is..............?


Gord Dibben MS Excel MVP

On Thu, 24 Sep 2009 09:19:06 -0700, ChrisR
wrote:

I want a given cell eg z100 to constantly - or at each recalculation - to
hold the contents - or reference - of ActiveCell (so a text box can highlight
it.). My code does it but only once at each run of the module, then it sticks
or throws an error.

If all you want is Z100 to return the activecell contents

Sub test()
Range("Z100) = ActiveCell.Value
End Sub


Gord


On Mon, 28 Sep 2009 10:04:01 -0700, ChrisR
wrote:

Homey suggested - I think -

Sub ActiveCell()
'
' ActiveCell Macro
' Macro recorded 28/09/2009 by Sheffield City Council
'

address of active cell at calc: =CELL("address")

contents: =INDIRECT(CELL("address"))

End Sub


But as a Macro this doesn't do anything except generate an error - "Compile
error Syntax error". I want it to paste the contents or reference of
ActiveCell into Z100, whenever I recalc (or when I run a Macro?)

Sorry to be naiive.

Chris

"Gord Dibben" wrote:

And the code is..............?


Gord Dibben MS Excel MVP

On Thu, 24 Sep 2009 09:19:06 -0700, ChrisR
wrote:

I want a given cell eg z100 to constantly - or at each recalculation - to
hold the contents - or reference - of ActiveCell (so a text box can highlight
it.). My code does it but only once at each run of the module, then it sticks
or throws an error.

Thanks a million. That does it.

Chris

"Gord Dibben" wrote:

If all you want is Z100 to return the activecell contents

Sub test()
Range("Z100) = ActiveCell.Value
End Sub


Gord


On Mon, 28 Sep 2009 10:04:01 -0700, ChrisR
wrote:

Homey suggested - I think -

Sub ActiveCell()
'
' ActiveCell Macro
' Macro recorded 28/09/2009 by Sheffield City Council
'

address of active cell at calc: =CELL("address")

contents: =INDIRECT(CELL("address"))

End Sub


But as a Macro this doesn't do anything except generate an error - "Compile
error Syntax error". I want it to paste the contents or reference of
ActiveCell into Z100, whenever I recalc (or when I run a Macro?)

Sorry to be naiive.

Chris

"Gord Dibben" wrote:

And the code is..............?


Gord Dibben MS Excel MVP

On Thu, 24 Sep 2009 09:19:06 -0700, ChrisR
wrote:

I want a given cell eg z100 to constantly - or at each recalculation - to
hold the contents - or reference - of ActiveCell (so a text box can highlight
it.). My code does it but only once at each run of the module, then it sticks
or throws an error.