Workdheet.BeforeChange

[jkr]

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

 

[Nigel]

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

 

[Jacob Skaria]

Why dont you get the formula even before these events.....say Activate...

 

[Mike H]

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

 

[joel]

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

 

[jkr]

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

 

[Nigel]

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
(e-mail address removed)



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

 

[Jan Kronsell]

Thank you.

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

 

[Jan Kronsell]

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

Jan

 

[Jan Kronsell]

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