Excel Event Before Cell Change

Posted on 2013-10-20
Medium Priority
Last Modified: 2013-10-24
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,

Question by:GeorgZH
LVL 37

Expert Comment

ID: 39585992

What would you use the event for?

Author Comment

ID: 39586103
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.

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.
LVL 81

Accepted Solution

byundt earned 2000 total points
ID: 39586143
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
    frmlaOld = cel.Formula
    cel.Formula = frmlaNew
    Application.Goto celNext
    Application.EnableEvents = True
    MsgBox frmlaOld
End If
End Sub

Open in new window


Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
In this article, I will demonstrate that how to do a PST migration from Exchange Server to Office 365. This method allows importing one single PST, or multiple PST's at once.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question