Excel Event Before Cell Change

Hi Experts,
Is there a way to capture the value of an Excel cell before it changes? The event Worksheet_Change returns the actual value. The event Worksheet_SelectionChange is fired if the user select another cell only, which is not always the case.

I thankful for any other idea,

Georg
GeorgZHAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Georg

What would you use the event for?
0
GeorgZHAuthor Commented:
Hi,
imagine a user defined function which reads and writes values to and from a database. The udf looks like this “=BudgetValue(Account, Period, Amount)”. The user can refresh the formula. Then the budget value is fetched from the database. If the user wants to change the budget value he shall overwrite the current amount with the new one. That’s the problem. If he does so, the prior formula gets overwritten by a figure. Therefore I need to save the formula somehow.

Example:
The user keys in cell A1 =BudgetValue(Salary,January,0). The cell shows a value of 0.
Next step, the user press the button ‘Refresh’. The budget for account Salary and period January is fetched from the database. The formula looks now like this: =BudgetValue(Salary,January,500). Cell A1 shows now a budget of 500 (assumption).

Next step, the user changes the budget to 600. He just overwrites the original value of 500 with 600. At this point I need the change event. It must read the old formula and replace the old value of 500 with 600. Afterwards the formula is =BudgetValue(Salary,January,600).

Next step, the press the button ‘Update Database’. The 600 are written to the DB.

This is all not a big thing. The only problem I face is to get the original formula before it is overwritten by the new amount.
0
byundtMechanical EngineerCommented:
Your Worksheet_Change sub can use the Application.Undo method to return to the previous state, capture the formula, then proceed with your update.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range, celNext As Range
Dim frmlaOld As String, frmlaNew As String
Set cel = Target.Cells(1)
If cel.MergeArea.Address = Target.Address Then
    frmlaNew = cel.Formula
    Set celNext = ActiveCell
    Application.EnableEvents = False
    Application.Undo
    frmlaOld = cel.Formula
    cel.Formula = frmlaNew
    Application.Goto celNext
    Application.EnableEvents = True
    MsgBox frmlaOld
End If
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.