• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Compare previous and current value of a cell

S

swensor

New Member
I have a table which requires that several cells' values need to be manually updated. This causes a cascade of calculations to be triggered and results are displayed.

As each of the manually-updated cell values are entered, I would like to display (by any means) the difference between the old and new cell values.

For example:

Cell A1 is the last recorded Dow Jones Index - 17097.00
Today, I update the A1 cell to the current DJI value - 18058.69

I would like to display (someplace ... anyplace ... a pop up comment window would be ideal) the difference between the two values: 18058.69 - 17097.00 = 961.69

I'm stumped. Any help would be appreciated. Thanks.

Ron
 
Hi Ron ,

The easiest way to do this is to use VBA ; is this acceptable ?

If yes , then please upload a sample workbook , with some data.

Narayan
 
Narayan,

Thank you so much. VBA would be great, but I'm not very good with it.

If its a routine that you could write without much time or trouble, I'd be forever grateful, but if its asking too much, I understand.

I was hoping that I could be shown some Excel function that would do the trick.

My workbook contains more than a decade of financials that would be a pain to de-identify, so I've attached a really dumbed down worksheet for demonstration purposes only.

Thank you for considering my question.

Ron
 

Attachments

  • Sample Worksheet.xls
    16.5 KB · Views: 15
Narayan,

Wow! Thanks.

I understand your code, and it appears that it should work perfectly. The call outs that appear in the workbook you returned are correct and are exactly what I want.

However ... it seems that I can't get the code to run. If I make any changes to the values in the cells, nothing happens. The call outs' values do not change if I change the value in any of the cells.

I have the security setting on my test box set to "low" so I can't figure out why the VB code doesn't run. Any thoughts?

Ron
 
Hi Ron ,

Are you running the code from the workbook I uploaded , or did you copy the code to your workbook , and you are trying to run it from there ?

If it is the latter , please note that there are :

Modules - Module1 to be copied
ThisWorkbook - Workbook_Open procedure to be copied
Sheet1 - Worksheet_Activate , Worksheet_SelectionChange and Worksheet_Change to be copied

When you open the workbook , the Workbook_Open procedure should run , and should initialize the values of the following two variables :

PrevValue , CurrCell

So before you make any changes in Sheet1 , you can type the following in the Immediate window :

?PrevValue , CurrCell

and see what is displayed ; if what is displayed does not reflect the contents and address of the cell where the cursor is , then it means that the Workbook_Open procedure has not executed ; in this case , you can type in :

Application.EnableEvents = True

and press the Enter key to ensure that event macros are enabled.

Close the workbook , and reopen it to see whether there is any change.

Narayan
 
Narayan,

It's perfect! Thank you so much.

My problem was that I failed to change the column range in the WorksheetChange and Worksheet_SelectionChange modules.

I've been getting by with work-arounds to VBA in Excel for years. It's probably time I learn it.

I'm grateful. Anything I can do for you?

Ron
 
You must log in or register to reply here.
Back
Top