Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Which VBA to use to track changes of a range of cells with a formula in it

0

Hi

I would like to have the below code amended. I have a some sheets in a workbook where I add dates in the range F2:F35 and in column G2:G35 it shows the due date then (for example F2 + 2 years), so every time I change the dates in the range F2:F35 it shows the new due date in column G2:G35. I would like to record the changes of the cells of the range F2:F35 to column P2:P35 and then to Q, R, S, T... without overwriting the old dates and only adding the dates that have changed, so that I have records of all changed dates. Is this possible? I tried to amend the below code with the ranges from C2 to F2:F35 and D2 to P2:P35, but it did not work.

I have now attached the sheet. The column F shows the current date when we overhauled parts and the column G shows the next due date (in 2 years). I want to record the dates when we overhauled parts to get a better understanding how often it was necessary to overhaul these parts before the next planned due date. I only want to record changes of the cells in column F (sorry, got the columns wrong when I posted it the first time)

Can anyone please help.

Dim xVal As String
Private Sub Worksheet_Change(ByVal Target As Range)
    Static xCount As Integer
    Application.EnableEvents = False
    If Target.Address = Range("C2").Address Then
        Range("D2").Offset(xCount, 0).Value = xVal
        xCount = xCount + 1
    Else
        If xVal <> Range("C2").Value Then
         Range("D2").Offset(xCount, 0).Value = xVal
        xCount = xCount + 1
        End If
    End If
    Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    xVal = Range("C2").Value
End Sub
 
Answer
Discuss

Discussion

Lina. Are the date in F5:F35 the orginal due dates but in G5:G5 the latest due dates (say using =EDATE(G5,24) for two years perhaps) with P, Q, R , T etc meant to show failed due dates/ previously promised due dates?

Also you have 
Dim xVal As String
but aren't you handling dates (which are really numbers)?

It might help if you could attach a file so we could see better what you're trying to do.
John_Ru (rep: 6092) Nov 19, '20 at 9:00 am
Add to Discussion

Answers

1
Selected Answer

Lina

Rather than modify your code, I've added the code below to the CIP sheet since I'm not sure all sheets are identical (but there is  a way it could be converted to a module and used across all workbooks, without copying it to each sheet).

If you change an entry in column F of CIP, it will capture the previous value and add it to a "history" cell in column P of the same row (rather that several columns since I figured changes every year or two don't really warrant extra columns). You'll see P2 is such a history (made by changing the F2 date to 1996, deleting P2 then making a few changes to F2).

For now it gives a confirming message (via the MsgBox line) but you can delete of comment that line out.

I've added some explanatory comments in the code below.

Hope this helps.

Private Sub Worksheet_Change(ByVal Target As Range)
'
' Move old dates to P if column F is changed
'

If Not Intersect(Target, Range("F:F")) Is Nothing Then

Dim NewWEDate, LastWEDate
NewWEDate = Target.Value 'save the new value

Application.EnableEvents = False ' stop anything happening for next few lines, important to avoid looping
Application.Undo  'get the previous value
LastWEDate = Target.Value
Target.Value = NewWEDate ' restore new value
Application.EnableEvents = True

Target.Offset(0, 10).Value = LastWEDate & "; " & Target.Offset(0, 10).Text
MsgBox Target.Address & " completed " & NewWEDate & " and previous " & LastWEDate & " added to " & Target.Offset(0, 10).Address

End If


End Sub

Also, as I suspected in the Discussion, I now note that Column E is always in months so you could replace the formula in G2 say: 

=IF(F2="","",DATE(YEAR(F2),MONTH(F2)+E2,DAY(F2)))

with the simpler:

=IF(F2="","",EDATE(F2, E2))

REVISION 1:

I've now attached a second file Copy of Parts list for overhaul new Macro works for all sheets v0_b.xlsm where a single macro works across all sheets (with the aid of a modified event macro. Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) has with two variables and calls SaveHistory(Sh, Target) both stored under Workbook.

The MsgBox is changed slightly, just to add the sheet name.

Discuss

Discussion

Hi
Thanks for that. The thing is, that it can be that we have to change the date after a few days or weeks, so it would be necessary to have the full history, even if it is only for once or twice a year. Is it possible to get that in the code as well?
Lina (rep: 2) Nov 19, '20 at 11:00 am
Lina. The code will build up history cell P no matter how frequently you change cell F. REVISION: Just put somethning in column Q if you don't want loads of dates to be visible (though they will be in the cell in P anyway

Also I'll shortly revise my answer so the same code works for all sheets- see revision comments.

Will remove company name too- I was only joking but understand your concern
John_Ru (rep: 6092) Nov 19, '20 at 11:18 am
Lina.

Just a thought but given the "all sheets" macro captures the last and new completion dates for Wet End Completion, it could be modified to calulate report the difference and (if the user wants it) automatically change the "Wet End Frequency" in column E to that calculated period.

Please ask another question if that appeals. Others may reply while I sleep!
John_Ru (rep: 6092) Nov 19, '20 at 11:55 am
That's a nice way to retrieve the cell's previous value. I used the SelectionChange event but UnDo looks a lot simpler.
Variatus (rep: 4889) Nov 19, '20 at 8:20 pm
I recommend: File > Info > Check for Issues > Inspect document > Inspect and Remove All to neuter uploaded files.
Variatus (rep: 4889) Nov 19, '20 at 8:25 pm
Thanks for your help. It works perfectly. You are a star ⭐
I was not joking, let me know where to send it to :)
Lina (rep: 2) Nov 20, '20 at 6:49 am
I'm glad that works for you (and guess my "Just a thought.." suggestion above isn't quite as helpful).

Thanks for selecting my answer. It's kind of you to offer also to send a reward (fruit juices) but I'm pretty sure that's against the Forum rules (right @Don?)
John_Ru (rep: 6092) Nov 20, '20 at 7:01 am
Hi Lina. I think Don's too busy on creating a VBA course to have replied to the implied question below. Rather than share details so openly here, do you have a group email address I might reach you by (i.e. one that's not specific to you)?
John_Ru (rep: 6092) Nov 23, '20 at 7:01 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login