Time stamp VBa does not work with AutoSum.. can i make it work?

[Flunkzz]

Hi everyone.

Please help me understand why my VBa does not work with AutoSum but it works when i put in figures manually in the field.

The VBa is as following

Private Sub Worksheet_Change(ByVal Target As Range)
' Adds Date when Status changes in Column G21
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("G21")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Value <> vbNullString Then
Target.Offset(-14, 7 - Target.Column).Value = Now
Target.Offset(0, 8 - Target.Column).NumberFormat = "dd/mm/yyyy"
Else
Target.Offset(0, 8 - Target.Column).ClearContents
End If
Application.EnableEvents = True
End Sub

So, When i add numbers to colum G21 manually, the VBa workd perfectly. but i would like it to work with G21 being a Autosum field so i can change the date when the sum is changing..

Please help guys! its a small but important fix

 

[Joe4]

Welcome to the Board!

That is how the Worksheet_Change event procedure works - it only works on cells that are manually changed. It does not work on values updated by formulas.

There is a Worksheet_Calculation event procedure which runs whenever a calculation is run on the worksheet. However, this has limitations. Although it can detect that a calculation was done, unlike the Worksheet_Change event procedure, it CANNOT tell which cell changed (hence, there is no "Target" argument with this function). So I don't think you could use this to make a Time Stamp for when the calculation in cell G21 updated, because it cannot tell you that.

Your best bet would probably be to use a Worksheet_Change event procedure, and watch the range that is used in the Sum formula.
For example, if your Sum formula is summing G2:G20, then have your Worksheet_Change event procedure watch the range G2:G20 for updates, and if any of them are updated, then update your Time Stamp.

 

[Flunkzz]

Welcome to the Board!

That is how the Worksheet_Change event procedure works - it only works on cells that are manually changed. It does not work on values updated by formulas.

There is a Worksheet_Calculation event procedure which runs whenever a calculation is run on the worksheet. However, this has limitations. Although it can detect that a calculation was done, unlike the Worksheet_Change event procedure, it CANNOT tell which cell changed (hence, there is no "Target" argument with this function). So I don't think you could use this to make a Time Stamp for when the calculation in cell G21 updated, because it cannot tell you that.

Your best bet would probably be to use a Worksheet_Change event procedure, and watch the range that is used in the Sum formula.
For example, if your Sum formula is summing G2:G20, then have your Worksheet_Change event procedure watch the range G2:G20 for updates, and if any of them are updated, then update your Time Stamp.

Hi.

Thank you for the clarification.

Could you possibly help with how that Worksheet_Calculation event procedure VBa would look like.

 

[Joe4]

Hi.

Thank you for the clarification.

Could you possibly help with how that Worksheet_Calculation event procedure VBa would look like.

I explained why a Worksheet_Calculation event procedure would NOT work for you in this case.
While that is the only event procedure that runs when a formula is re-calculated (its value updated), it can only detect that a calculation was done somewhere on the sheet, and cannot pinpoint exactly which formulas values updated. Hence, it would not do what you want.

I recommended using a Worksheet_Change procedure, but watch the cells that are used in the Sum formula in cell G21.
If you need help with that, please let us know the exact formula in cell G21.

 

[Flunkzz]

I explained why a Worksheet_Calculation event procedure would NOT work for you in this case.
While that is the only event procedure that runs when a formula is re-calculated (its value updated), it can only detect that a calculation was done somewhere on the sheet, and cannot pinpoint exactly which formulas values updated. Hence, it would not do what you want.

I recommended using a Worksheet_Change procedure, but watch the cells that are used in the Sum formula in cell G21.
If you need help with that, please let us know the exact formula in cell G21.

Yes, sorry, thats what i meant.

Could you help me make that for Cell G21 and if it could return with a timestamp in cell G7?? or is that not possible

 

[Flunkzz]

I explained why a Worksheet_Calculation event procedure would NOT work for you in this case.
While that is the only event procedure that runs when a formula is re-calculated (its value updated), it can only detect that a calculation was done somewhere on the sheet, and cannot pinpoint exactly which formulas values updated. Hence, it would not do what you want.

I recommended using a Worksheet_Change procedure, but watch the cells that are used in the Sum formula in cell G21.
If you need help with that, please let us know the exact formula in cell G21.

Are you able to help with this VBA?

i cant seem to make it happen..

 

[Joe4]

I can help you, if you give me what I asked for.

If you need help with that, please let us know the exact formula in cell G21.