Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Lounger
    Join Date
    Mar 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Tracking Data Entries by Date (Excel 2003)

    Maybe someone has an idea how to do the following.

    I have a number of tables that summarize forecasts for the next 24 months. These are hard data that are revised often. Sometimes I revise the fist 4 months, sometime six to eight months out and sometime the whole series (depending on the timeline requested). It would be helpful to know at any given time when, say the January 2006 forecast for a specific item, was last updated. I would like to have a column next to each forecast column that indicates when the forecast was last changed. The idea is that whenever I type a new value in say cell F1895, the value in cell G1895 is updated with the date when the change occurred. I hope this makes sense and maybe there is an if statement somewhere that could accomplish this or some other way that I am not aware of. Any help is greatly appreciated.

    Thank you,

    Altin

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Tracking Data Entries by Date (Excel 2003)

    Welcome to Woody's Lounge!

    You can use the Worksheet_Change event of the worksheet containing the forecasts:
    - Format column G with a date format.
    - Right-click the sheet tab of the worksheet.
    - Select View Code from the popup menu.
    - Enter the following code (you can copy/paste it from this reply):

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("F:F")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("F:F")).Cells
    oCell.Offset(0, 1) = Date
    Next oCell
    End If
    Set oCell = Nothing
    End Sub

    If you want the cell in column G to contain the date and time instead of just the date, use

    oCell.Offset(0, 1) = Now

    and format column G with a date and time format.

    Note: if your macro security is set to High, the code will not be executed unless you sign the VBA code with a digital certificate. If you set macro security to Medium, you'll be prompted whether you want to allow macros to run when you open the workbook.

  3. #3
    Lounger
    Join Date
    Mar 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tracking Data Entries by Date (Excel 2003)

    Fantastic! Thank you very much Hans.

    Altin

  4. #4
    Lounger
    Join Date
    Mar 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tracking Data Entries by Date (Excel 2003)

    One more question if I may. While the code I received for Hans works great when I make changes to one column and get an update in the adjacent column, how can the code be modified so that I can track changes in columns C, E, G, I (etc...) and receive a corresponding date value in adjacent columns (D, F, H, J (etc..). Again, thanks in advance for all the help.

    Altin

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Tracking Data Entries by Date (Excel 2003)

    Try this code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    For Each oCell In Target.Cells
    If oCell.Column > 1 And oCell.Column Mod 2 = 1 Then
    oCell.Offset(0, 1) = Date
    End If
    Next oCell
    Set oCell = Nothing
    End Sub

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tracking Data Entries by Date (Excel 2003)

    Try this modification to Hans' code.

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Union(Range("C:C"), Range("E:E"), _
    Range("G:G"), Range("I:I"))) Is Nothing Then
    For Each oCell In Intersect(Target, Union(Range("C:C"), Range("E:E"), _
    Range("G:G"), Range("I:I"))).Cells
    Application.EnableEvents = False
    oCell.Offset(0, 1) = Date
    Application.EnableEvents = True
    Next oCell
    End If
    Set oCell = Nothing
    End Sub
    </pre>


    Hans' modification is going to work for more than just columns C, E, G, and I.
    Legare Coleman

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Tracking Data Entries by Date (Excel 2003)

    From "in columns C, E, G, I (etc...)" I assumed that Altin wanted it to work on more than columns C, E, G and I.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tracking Data Entries by Date (Excel 2003)

    I agree that it could be read that way. I guess he can choose whichever code does what he wants.

    I would recommend that you disable events when you store the date though to save a trip through the event routine.
    Legare Coleman

  9. #9
    Lounger
    Join Date
    Mar 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tracking Data Entries by Date (Excel 2003)

    Thanks to both Lagare and Hans. Hans is right as I did need to know how to make it work on more than 4 columns. The only drawback to having it apply to the entire sheet is that if I did want to make any changes to any other sections in the sheet, it would add a corresponding date (thus erasing some of the info). I guess this is a problem when making such changes to a spreadsheet after it was designed. Do you think it is possible to make it work on a set range say $C$3000:$BM$3500? The code from Hans works fine, it just would be easier in case I needed to make any changes to some of the data in the top part of the sheet. Again, thanks much.

    Altin

  10. #10
    Lounger
    Join Date
    Mar 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tracking Data Entries by Date (Excel 2003)

    Great, thank you. - Altin

    PS: For whoever may use the last instance in the thread, please note to close the Intersect with two )).

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Tracking Data Entries by Date (Excel 2003)

    Ah, thanks, I didn't really test the code. I have corrected my mistake.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Tracking Data Entries by Date (Excel 2003)

    Edited by HansV to add missing closing parentheses - thanks to Altin

    A combination of the various techniques demonstrated in this thread should do what you want:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("C3000M3500")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("C3000M3500")).Cells
    If oCell.Column Mod 2 = 1 Then
    oCell.Offset(0, 1) = Date
    End If
    Next oCell
    End If
    Set oCell = Nothing
    End Sub

  13. #13
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tracking Data Entries by Date (Excel 2003)

    Hans and Legare,

    This looks like something that can be adapted for a need I have. I've built an Excel 2002 (SP3) Case Tracking workbook with two worksheets: Cases - member master records , and Transactions - detail activity records for each member by date (I know, this database should be built in Access, but politics dictated this course for now). When a new Case (row) is added to the Cases worksheet, I need to capture and hold the current date in a column on that row.

    I tried to modify your code to add the date to column Q whenever data was entered into any of columns B to H, but I couldn't get it to work. There were no errors, the code just failed to put the date into any cell on the row. I then decided to try testing the various Worksheet_Change routines (exactly as both of you had listed them in this thread) in a blank worksheet, and got the same result, no error, but no function, either.

    I noted that Altin had originally specified he was using Excel 2003. Are these routines dependent upon Excel 2003, and can they be adapted to 2002? Or, am I just doing something wrong -- missing a step or something like that (I've learned a lot about VBA lately, but for all intents and purposes, I'm still a novice)?

    Any help is appreciated,

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Tracking Data Entries by Date (Excel 2003)

    The Worksheet_Change procedure is an event handler. It MUST be put in the module belonging to the worksheet. The code won't do anything at all if you put it in a standard module (the kind you create by selecting Insert | Module in the Visual Basic Editor). The easiest way to open a worksheet module is to right-click the sheet tab and select View Code from the shortcut menu that pops up.

  15. #15
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tracking Data Entries by Date (Excel 2003)

    Hans,

    Thank you for responding so quickly. Originally, I had entered the procedure in the worksheet's module using the right click on the worksheet tab, as you had described in your first response to Altin (<post#=461058>post 461058</post#> ). After reading your response, I restarted Excel, opened my test workbook, and (just for kicks) entered some text into column F. Low and behold, a date appeared in column G of the same row. That got me considering why the procedure would now be working when I couldn't get it to work before.

    After several minutes of experimentation, I discovered the following:

    <UL><LI>After entering the first event procedure to a worksheet, I must save the workbook, close it, then reopen it to make it functional.
    <LI>Once functioning, the procedure can be modified and immediately used, without restarting the worksheet.
    <LI>Subsequent procedures can be added and modified, then immediately used, without restarting the worksheet.[/list] I'm not sure if this is typical of all Excel installations, or just a quirk of our corporate installation, or just the way my computer's set up. But, I thought it might be a good idea to let others know about my experience, in case they run into something similar. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    The good news is that the procedure works for me. Now, I need to challenge my VBA skills to modify it to recognize only changes in columns B through H, and post the date, specifically, in column Q.

    As always, your patient guidance and abundant knowledge are greatly appreciated,

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •