Timestamp not calculating when referencing a calculated (IF) cell

**gregcarroll** · 01-24-2012, 11:50 PM

I have read dozens of posts regarding McGimpsey solution to the timestamp problem (http://www.mcgimpsey.com/excel/timestamp.html)

His VBA solution works perfectly if you type a value in the reference cell.

However if the reference cell contains a formula then nothing happens.

eg:

Col B: =if(A1>50,1,0) etc . .
Col C: Uses McGimpsey VBA code to update C1 when A1>50

The Col C timestamp only works if I type "1" into Col B. Nothing happens otherwise.

My actual problem is much more complex than shown above - but I hope this illustrates the point.

Any ideas how to overcome this problem.

**jeffreybrown** · 01-24-2012, 11:55 PM

If the change is the result of a formula then take a look at the calculate event

**gregcarroll** · 01-24-2012, 11:57 PM

Hi Jeff,

Could you please elaborate so I dont have to search through a few dozen more forums. I am new to VBA in Excel.

Greg

**jeffreybrown** · 01-25-2012, 12:03 AM

Hi Greg,

Maybe this will help

http://dmcritchie.mvps.org/excel/event.htm#calculate

From Excel help

Worksheet.Calculate Event
Occurs after the worksheet is recalculated, for the Worksheet object.
Syntax

expression.Calculate

expression A variable that represents a Worksheet object.

Return Value
nothing

Example

This example adjusts the size of columns A through F whenever the worksheet is recalculated.

Please Login or Register  to view this content.

**gregcarroll** · 01-25-2012, 12:22 AM

Thanks - now I understand.

However, I am now getting Run-time Error 424 - Object Required, yet I have not used any variables in my code and it works with no errors when under Worksheet_Change

**jeffreybrown** · 01-25-2012, 01:10 PM

What code are you using?

**gregcarroll** · 01-26-2012, 08:27 PM

The code I am using is as follows:

Please Login or Register  to view this content.

Regards,
Greg

**jeffreybrown** · 01-26-2012, 09:02 PM

Would this work for you?

Please Login or Register  to view this content.

**gregcarroll** · 01-26-2012, 09:32 PM

Thanks Jeff,

Your code works nicely to solve the run-time error, but the original problem remains - the timestamp will only update in response to a manual entry. Nothing happens if the entry is produced as a result of a formula.

I have attached a test spreadsheet so you can take a look yourself.

This appears to be one of those deceptively difficult problems to solve.

Greg

Timestamp_test_spreadsheet.xlsm

**p24leclerc** · 01-26-2012, 10:27 PM

Here is a macro that should work as you want.
It actually looks at the target and makes sure it is within the whole table. I think it should only looks at column A and B but this is something you could change yourself. If value of the row of the target cell and column C is 1 then the cell of that row and column D will be set to actual date.

Please Login or Register  to view this content.

**jeffreybrown** · 01-26-2012, 10:43 PM

Sorry about that Greg I though for sure I had the Worksheet_Calcualte event.

I'm not all that sure at how to solve this one. Let me ask the experts how they would approach this.

**JBeaucaire** · 01-27-2012, 09:36 AM

You'll need to store the current values of the cells off somewhere they can be "checked" against the current values in the watched range(s). So, I'm going to assume we can use column AA to watch A, AE to watch E, and AI to watch I, 26 columns to the right.

Please Login or Register  to view this content.

**gregcarroll** · 02-02-2012, 02:27 AM

Awesome - you are a legend! Many thanks!

**gregcarroll** · 02-07-2012, 06:50 AM

Not quite solved.

JBeaucaire's code does a great job of watching the code and placing the timestamps. However, every time you open excel the cells which have previously been timestamped update to today's date. Clearly this is not what was intended!

Any ideas how to solve this little problem?

The latest code (as used in the attached spreadsheet) is as follows:

Please Login or Register  to view this content.

**JBeaucaire** · 02-07-2012, 08:59 AM

The code as shown does not change any value in column D unless the values in column C and column E no longer match. I see no reason to think they would change. I manually set the column D values back to a few days ago, then saved and closed/opened the file multiple times and the dates did not change.