Hi,

I've recently started writing functions in Excel which I then call from
cells to perform calculations etc. However, I am aware that a function
cannot alter the value of *another* cell. What I want to do is this :

<Cell 1 is usually empty.
When <Cell 1 changes from empty to any other value, take a snapshot of
<Cell 2 (which updates constantly) and copy it into <Cell 3.
<Cell 2 will then continue to update as usual, whilst <Cell 3 will contain
a static value.
Then when <Cell 1 becomes empty again, erase <Cell 3 and that's it.
Then keep repeating this whole process as long as the spreadsheet is open.

The concept sounds simple enough and I'm sure it must be possible but I
can't for the life of me figure out how! All I've been using so far is
functions, which take arguments and return values. Any help appreciated!

Many thanks,

Tom

VBA functions can change the value of "another cell" they really can do just
about anything.

However what you want here is code in your Workbook_SheetChange event
From VBE choose thisworkbook
pulldown Sheetchange
insert code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
dim myTargetCellOne as range
dim myTargetCellTwo as range
dim myTargetCellThree as range

set myTargetCellOne = 'cell1 address here
set myTargetCellTwo = 'cell2 address here
set myTargetCellThree = 'cell3 address here

If Target = myTargetCellOne Then
If myTargetCellOne.value = "" Then
myTargetCellThree.clearcontents
Exit Sub
Else myTargetCellThree.value = myTargetCellTwo.value
end if
end if

End Sub

"Tom" wrote:

Hi,

I've recently started writing functions in Excel which I then call from
cells to perform calculations etc. However, I am aware that a function
cannot alter the value of *another* cell. What I want to do is this :

<Cell 1 is usually empty.
When <Cell 1 changes from empty to any other value, take a snapshot of
<Cell 2 (which updates constantly) and copy it into <Cell 3.
<Cell 2 will then continue to update as usual, whilst <Cell 3 will contain
a static value.
Then when <Cell 1 becomes empty again, erase <Cell 3 and that's it.
Then keep repeating this whole process as long as the spreadsheet is open.

The concept sounds simple enough and I'm sure it must be possible but I
can't for the life of me figure out how! All I've been using so far is
functions, which take arguments and return values. Any help appreciated!

Many thanks,

Tom

What causes cell1 to change?

--
Regards,
Tom Ogilvy


"Tom" wrote in message
...
Hi,

I've recently started writing functions in Excel which I then call from
cells to perform calculations etc. However, I am aware that a function
cannot alter the value of *another* cell. What I want to do is this :

<Cell 1 is usually empty.
When <Cell 1 changes from empty to any other value, take a snapshot of
<Cell 2 (which updates constantly) and copy it into <Cell 3.
<Cell 2 will then continue to update as usual, whilst <Cell 3 will
contain
a static value.
Then when <Cell 1 becomes empty again, erase <Cell 3 and that's it.
Then keep repeating this whole process as long as the spreadsheet is open.

The concept sounds simple enough and I'm sure it must be possible but I
can't for the life of me figure out how! All I've been using so far is
functions, which take arguments and return values. Any help appreciated!

Many thanks,

Tom

That's great, thankyou so much. Actually I had to do a bit of hunting
around as myTargetCellOne is actually updated via DDE link, not manually.
But I found Workbook_SheetCalculate which works very well. I love this
event stuff!

Tom

"Vacation's Over" wrote in message
...
VBA functions can change the value of "another cell" they really can do
just
about anything.

However what you want here is code in your Workbook_SheetChange event
From VBE choose thisworkbook
pulldown Sheetchange
insert code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
dim myTargetCellOne as range
dim myTargetCellTwo as range
dim myTargetCellThree as range

set myTargetCellOne = 'cell1 address here
set myTargetCellTwo = 'cell2 address here
set myTargetCellThree = 'cell3 address here

If Target = myTargetCellOne Then
If myTargetCellOne.value = "" Then
myTargetCellThree.clearcontents
Exit Sub
Else myTargetCellThree.value = myTargetCellTwo.value
end if
end if

End Sub

"Tom" wrote:

Hi,

I've recently started writing functions in Excel which I then call from
cells to perform calculations etc. However, I am aware that a function
cannot alter the value of *another* cell. What I want to do is this :

<Cell 1 is usually empty.
When <Cell 1 changes from empty to any other value, take a snapshot of
<Cell 2 (which updates constantly) and copy it into <Cell 3.
<Cell 2 will then continue to update as usual, whilst <Cell 3 will
contain
a static value.
Then when <Cell 1 becomes empty again, erase <Cell 3 and that's it.
Then keep repeating this whole process as long as the spreadsheet is
open.

The concept sounds simple enough and I'm sure it must be possible but I
can't for the life of me figure out how! All I've been using so far is
functions, which take arguments and return values. Any help appreciated!

Many thanks,

Tom