Have you ever wanted your Microsoft Excel spreadsheet to change a cell after you enter a number into it? You would think you could write a formula that notices the value is different then alters another cell’s contents, right?
This is a very common requirement from Excel users and…You just can’t.
A cell can NOT alter the value of another cell, and it can not change or replace itself.
But there is a solution!
Problem Solved …?
What you need to do to achieve this effect is to use the Change event of the sheet to find out when your cell contents are modified. Once this event is triggered then you can write new information into your target cells.
The problem is even that does not work out to be entirely simple in practice, because entering new content within a simple change event will of course change the values of the cells, creating an infinite loop!
The Solution
To overcome this paradox we need to employ a cunning trick …
If you change the cell within the change event it will trigger another change event and basically put you in an infinite loop UNLESS you use a public boolean variable called something like ‘blnChangeEventInProgress’ to flag when you are in a change event.
When the change event is triggered, check to see if this flag is set. If it is set then the change event should not change the value in the cell and would unset the flag. If the flag is unset than the change event would set the flag to true and make the required changes.
The Code
Let’s write some example code to illustrate the idea.
Imagine we have a cinema chain and want to track ticket sales. We would enter the number of tickets sold in each cinema and the spreadsheet would automatically translate the number of tickets, for example 7, into the value of those tickets, ie. $49.
The action takes place in the Worksheet_Change event. This event is passed the Target, which we use to find the column and row.
If our flag is set then we set it to false and apply the revision to our cells. We have a TicketPrice (currently 7 dollars) to make it easy to understand the code and modify the price should we need to ever increase it. This ticket price is used to set the Product of the target cell.
Otherwise, if the flag is False, we simply set it to True.
Summary
While you can not simply use a function in Microsoft Excel to revise the current cell, as you can see we can get a similar result but we do need to think outside the box somewhat and mitigate some potential problems!
P.S.
Don’t forget to check out our PDF to Excel Converter. It can save you a lot of precious time and improve your productivity.
