Can You Track Date of Changes Made on Worksheets & Link The Date Back to an Index Page?

[Lexi7]

Hi Everyone,
I have an index worksheet that shows a list of (50+) companies that are linked to their individual worksheets. On their individual worksheets, we have to show the date our salesmen talked/interacted with them, their business contact, number, topic discussed, and the follow up action to be performed (call them next week, monthly reminder to contact, etc.) I'd love if I could get the index sheet to show the follow up action & when it's due next to the company name but I'll be more than happy if I could just get it to show when a change was last made. I've tried a few different ways from online but no luck. Any ideas/suggestions would be greatly appreciated!

 

[JoeMo]

Here are two modules, one for the sheet named "Index" that will list all other sheets in col A and date of last change in col B, and the another for Thisworkbook that will date stamp changes made to any sheet other than the Index sheet. The date stamp in this code goes into cell N1, but you can change that to any cell you wish. Cell A1 of each sheet will have a return link to the Index sheet, but you can use any other cell you wish.

The first code goes into the index sheet.
To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim n As Integer
Dim calcState As Long, scrUpdateState As Long
Dim LastCellChanged As String
calcState = Application.Calculation
Application.Calculation = xlCalculationManual
scrUpdateState = Application.ScreenUpdating
Application.ScreenUpdating = False
n = 1

    With Me
        .Columns(1).ClearContents
        .Cells(1, 1) = "INDEX"
        .Cells(1, 1).Name = "Index"
        .Cells(1, 2).Value = "Last Cell Changed"
    End With
    
    For Each wSheet In Worksheets
        If wSheet.Name <> Me.Name Then
            LastCellChanged = wSheet.Range("N1").Value
            n = n + 1
                With wSheet
                    .Range("A1").Name = "Start_" & wSheet.Index   'Change return link cell to suit here and next line
                    .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
                    SubAddress:="Index", TextToDisplay:="Back to Index"
                End With
                
                Me.Hyperlinks.Add Anchor:=Me.Cells(n, 1), Address:="", _
                SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
                Me.Cells(n, 2).Value = LastCellChanged
        End If
    Next wSheet
Me.Columns("A:B").AutoFit
Application.Calculation = calcState
Application.ScreenUpdating = scrUpdateState
End Sub

The code below goes into Thisworkbook.
To install the code:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and double-click the 'Thisworkbook' icon.
3. Copy the code below from your browser window and paste it into the white space in the VBE window.
4. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
5. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Index" Then Exit Sub
If Target.Address = "$A$1" Then Exit Sub
Application.EnableEvents = False
Sh.Range("N1").Value = Now  'Change the cell where last cell change time will be stored to suit
Application.EnableEvents = True
End Sub

 

[Lexi7]

Thank you so much JoeMo for taking the time to respond, I really appreciate your detailed instructions as I'm pretty new to VBA. I apologize in advance, I should have said I had found a macro online that created & applied formatting for new company sheets which linked back to an index, I'm sorry if I wasted your time. I copied your code and it worked the first time I used it in the workbook with that macro but it also inserted over the company names on the sheets. When I tried to use your code on the same workbook without index macro, it resulted in a "Compile Error: Invalid use of Me Keyword" message on the "With Me" line. I followed your directions exactly when I put it on the second workbook so I'm not sure why the message appeared this time and not the first. Once again I'm sorry, I should have mentioned the macro I was using in my first post.

 

[JoeMo]

No problem. Sounds like you didn't put the macros in the right place despite my directions.

 

[Lexi7]

Hi JoeMo, I'm so sorry I haven't responeded sooner, I've been off work dealing with health problem. You are correct in that I wasn't putting the first macro in the right place because I was trying to combine it with my macro. The index macro I'm working with deletes and recreates the index tab every time it runs. I've had some success in combining your first macro with it in that the dates will appear on the index. The problem I'm now facing is that the dates that do appear are one row below the company they're associated with. I've tried experimenting with it but haven't have much success (either the date column gets deleted completely, the company name above that row appears in the date box, it disappears, moves over several columns, etc.) I don't know much about vba, but the only thing I can think of is that I have

[

Set wsIndex = Worksheets.Add(Before:=Sheets(1))

in my macro that may be causing the offset. I also can't get it to automatically update, either on the company's worksheet or index unless I run my macro first. If you have any ideas or suggestions on where to look, I'd really appreciate it. Thank you again for the time and attention you've already spent on this!

 

[JoeMo]

Hi JoeMo, I'm so sorry I haven't responeded sooner, I've been off work dealing with health problem. You are correct in that I wasn't putting the first macro in the right place because I was trying to combine it with my macro. The index macro I'm working with deletes and recreates the index tab every time it runs. I've had some success in combining your first macro with it in that the dates will appear on the index. The problem I'm now facing is that the dates that do appear are one row below the company they're associated with. I've tried experimenting with it but haven't have much success (either the date column gets deleted completely, the company name above that row appears in the date box, it disappears, moves over several columns, etc.) I don't know much about vba, but the only thing I can think of is that I have

[

Set wsIndex = Worksheets.Add(Before:=Sheets(1))

in my macro that may be causing the offset. I also can't get it to automatically update, either on the company's worksheet or index unless I run my macro first. If you have any ideas or suggestions on where to look, I'd really appreciate it. Thank you again for the time and attention you've already spent on this!

The code I provided deletes and recreates the index list every time the Index sheet is activated, and it will put the dates in the correct place. All you need to do is create a tab named "Index" and follow the instructions I provided to install the code. I can appreciate that you have existing code that you are comfortable with, but I think you will find what I provided will meet your requirements. Why not make a copy of your workbook and try it on the copy first?

 

[Lexi7]

JoeMo, you are AWESOME! I did as you suggested and it worked perfectly. I made a few changes for the Index code (freeze panes, font, etc.) but it was much easier than trying to incorporate it into my original macro. Thank you so much for your patience and suggestions, they really helped me understand more about vba. Thanks again!!!!

 

[JoeMo]

JoeMo, you are AWESOME! I did as you suggested and it worked perfectly. I made a few changes for the Index code (freeze panes, font, etc.) but it was much easier than trying to incorporate it into my original macro. Thank you so much for your patience and suggestions, they really helped me understand more about vba. Thanks again!!!!

Thanks for your willingness to try another approach, and for letting me know it works for you.