asked on

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

Norie

Georg

What would you use the event for?

GeorgZH

ASKER

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.

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial