When I use

Private Sub Worksheet_Change(ByVal Target As Range)

og a cell containing a formula, Target.Formula is the Formula after
the change has happened. Is there any way to get the Formula that is
in the cell BEFORE the change happens? I cannot use

Private Sub Worksheet_SelectionChange(ByVal Target As Range) to grab
the formula, as the user is not necessarilu selecting the cell before
the change.

Regards
Jan

No, by definition it is the selection change in the worksheet cell that
triggers the event. If the user is changing something they should not,
consider locking the relevant cell(s) and protecting the sheet.

You could use the Worksheet_SelectionChange(ByVal Target As Range), this
tells you the cell(s) target just entered.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Target.Formula
End Sub

--

Regards,
Nigel




wrote in message
...
When I use

Private Sub Worksheet_Change(ByVal Target As Range)

og a cell containing a formula, Target.Formula is the Formula after
the change has happened. Is there any way to get the Formula that is
in the cell BEFORE the change happens? I cannot use

Private Sub Worksheet_SelectionChange(ByVal Target As Range) to grab
the formula, as the user is not necessarilu selecting the cell before
the change.

Regards
Jan

Why dont you get the formula even before these events.....say Activate...
--
If this post helps click Yes
---------------
Jacob Skaria


" wrote:

When I use

Private Sub Worksheet_Change(ByVal Target As Range)

og a cell containing a formula, Target.Formula is the Formula after
the change has happened. Is there any way to get the Formula that is
in the cell BEFORE the change happens? I cannot use

Private Sub Worksheet_SelectionChange(ByVal Target As Range) to grab
the formula, as the user is not necessarilu selecting the cell before
the change.

Regards
Jan

Hi,

You can use worksheet_selectionchange. When a cell is seleceted using this
event capture the value. A very simple example of that is below and it
doesn't matter if the cell isn't changes you simply don't use the variable.

Public lastval As String
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox lastval
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
lastval = Target.Value
End Sub

Mike



" wrote:

When I use

Private Sub Worksheet_Change(ByVal Target As Range)

og a cell containing a formula, Target.Formula is the Formula after
the change has happened. Is there any way to get the Formula that is
in the cell BEFORE the change happens? I cannot use

Private Sub Worksheet_SelectionChange(ByVal Target As Range) to grab
the formula, as the user is not necessarilu selecting the cell before
the change.

Regards
Jan

You can use undo

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
NewData = Target.Formula

Application.Undo
OldData = Target.Formula
Target.Formula = NewData
Application.EnableEvents = True

End Sub

" wrote:

When I use

Private Sub Worksheet_Change(ByVal Target As Range)

og a cell containing a formula, Target.Formula is the Formula after
the change has happened. Is there any way to get the Formula that is
in the cell BEFORE the change happens? I cannot use

Private Sub Worksheet_SelectionChange(ByVal Target As Range) to grab
the formula, as the user is not necessarilu selecting the cell before
the change.

Regards
Jan

I get that, but preventing the change is not the isue here.

I have three cells (actually about 100) with circular references
between them, like in A1: =A1*A2 and in A2: A2: =A1-A3 and in A3:
=A2+A1
(These are not the actual formulas) . Now I want the user to be able
to enter a value in any of the cells, have the other cells calculated
and the the formula in the cell where the value was entered, resat to
the original formula.

Iteratiobs are on, and number of iterations are set to 1.

Jan


On 2 Apr., 12:35, "Nigel" wrote:
No, by definition it is the selection change in the worksheet cell that
triggers the event. *If the user is changing something they should not,
consider locking the relevant cell(s) and protecting the sheet.

You could use the Worksheet_SelectionChange(ByVal Target As Range), this
tells you the cell(s) target just entered.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
* MsgBox Target.Formula
End Sub

--

Regards,
Nigel


wrote in message

...



When I use

Private Sub Worksheet_Change(ByVal Target As Range)

og a cell containing a formula, Target.Formula is the Formula after
the change has happened. Is there any way to get the Formula that is
in the cell BEFORE the change happens? I cannot use

Private Sub Worksheet_SelectionChange(ByVal Target As Range) to grab
the formula, as the user is not necessarilu selecting the cell before
the change.

Regards
Jan- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

As I said then use the Worksheet Change event to capture the contents before
the change, however on the next iteration this will be overwritten, and from
what you now tell us it could be any one of 100+ cells. I would store all
formula for all cells in an array (or copy worksheet). Let the user change
something and then have a Sheet control to re-copy all formula back. Do not
use events as keeping track of changes is a challenge.

--

Regards,
Nigel




wrote in message
...
I get that, but preventing the change is not the isue here.

I have three cells (actually about 100) with circular references
between them, like in A1: =A1*A2 and in A2: A2: =A1-A3 and in A3:
=A2+A1
(These are not the actual formulas) . Now I want the user to be able
to enter a value in any of the cells, have the other cells calculated
and the the formula in the cell where the value was entered, resat to
the original formula.

Iteratiobs are on, and number of iterations are set to 1.

Jan


On 2 Apr., 12:35, "Nigel" wrote:
No, by definition it is the selection change in the worksheet cell that
triggers the event. If the user is changing something they should not,
consider locking the relevant cell(s) and protecting the sheet.

You could use the Worksheet_SelectionChange(ByVal Target As Range), this
tells you the cell(s) target just entered.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Target.Formula
End Sub

--

Regards,
Nigel


wrote in message

...



When I use

Private Sub Worksheet_Change(ByVal Target As Range)

og a cell containing a formula, Target.Formula is the Formula after
the change has happened. Is there any way to get the Formula that is
in the cell BEFORE the change happens? I cannot use

Private Sub Worksheet_SelectionChange(ByVal Target As Range) to grab
the formula, as the user is not necessarilu selecting the cell before
the change.

Regards
Jan- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Thank you.

Jan

joel wrote:
You can use undo

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
NewData = Target.Formula

Application.Undo
OldData = Target.Formula
Target.Formula = NewData
Application.EnableEvents = True

End Sub

" wrote:

When I use

Private Sub Worksheet_Change(ByVal Target As Range)

og a cell containing a formula, Target.Formula is the Formula after
the change has happened. Is there any way to get the Formula that is
in the cell BEFORE the change happens? I cannot use

Private Sub Worksheet_SelectionChange(ByVal Target As Range) to grab
the formula, as the user is not necessarilu selecting the cell before
the change.

Regards
Jan

Because I don't know which cell the user will change, before he actually
changes it.

Jan

Jacob Skaria wrote:
Why dont you get the formula even before these events.....say
Activate...

When I use

Private Sub Worksheet_Change(ByVal Target As Range)

og a cell containing a formula, Target.Formula is the Formula after
the change has happened. Is there any way to get the Formula that is
in the cell BEFORE the change happens? I cannot use

Private Sub Worksheet_SelectionChange(ByVal Target As Range) to grab
the formula, as the user is not necessarilu selecting the cell before
the change.

Regards
Jan

The problem is, that the cell is not always selected. Maybe the change
happens in the cell that is already active, when the workbook is opened.

Jan

Mike H wrote:
Hi,

You can use worksheet_selectionchange. When a cell is seleceted using
this event capture the value. A very simple example of that is below
and it doesn't matter if the cell isn't changes you simply don't use
the variable.

Public lastval As String
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox lastval
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
lastval = Target.Value
End Sub

Mike



" wrote:

When I use

Private Sub Worksheet_Change(ByVal Target As Range)

og a cell containing a formula, Target.Formula is the Formula after
the change has happened. Is there any way to get the Formula that is
in the cell BEFORE the change happens? I cannot use

Private Sub Worksheet_SelectionChange(ByVal Target As Range) to grab
the formula, as the user is not necessarilu selecting the cell before
the change.

Regards
Jan