Consulting

Results 1 to 9 of 9

Thread: Time stamp in a fixed cell when content of specific cells in row are changed

  1. #1

    Time stamp in a fixed cell when content of specific cells in row are changed

    Hello.

    I've looked all over this site as well as others for a solution and unable to locate a solution. My VBA skills are sorely lacking so I'm unable to figure out how to make this work from what others have used.

    So this code works fine as is but it doesn't do exactly what I want. It originally was updating cells in the same column below the watched cell based on the offset specified. I managed to alter it to update the cell based on the offset in the same row. The result here is that updates for column F put a time stamp in column O, which is EXACTLY what I want. However, due to the fact that it's taking the offset for each watched cell, the update made to column G put a time stamp in column P, H into Q and so on.

    What I would like is that any updates made to F, G, H, I, J, M & N result in a time stamp being placed in O on the same row.

    Private Sub Worksheet_Change(ByVal Target As Range)    Dim rngStatus As Range
        Dim c As Range
        Dim rngChange As Range
    
    
        'Which row has the status cells we want to monitor?
        Set rngStatus = Range("F:J,M:N")
    
    
        Set rngChange = Intersect(rngStatus, Target)
        
        'Did user change a cell we care about?
        If rngChange Is Nothing Then Exit Sub
        
        Application.ScreenUpdating = False
        Application.EnableEvents = False
    
    
        'Put timestamp in column O if column F is changed
        rngChange.Offset(columnOffSet:=9).Value = Now
    
    
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End Sub
    Any assistance would be greatly appreciated.

    Please let me know if any further clarification is needed.
    EXCEL 2016
    OS: Windows 7 Ent

    Regards,

    Nathan


  2. #2
    And by all means, if the above method isn't the most efficient way to achieve my goal, I am totally open to something else entirely. This is the closest I could get to what I wanted but I realize it may not be ideal.

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Private Sub Worksheet_Change(ByVal Target As Range)   
        Application.EnableEvents = False 
         
        If Intersect((Range("F:J,M:N"), Target) is nothing then Exit Sub
        Cells(Target.Row, "O") = Now
         
        Application.EnableEvents = True 
    End Sub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    Thank you SamT for the reply.

    When I update the code with what you've posted, the IF line is red and I get a compile error stating that a close parenthesis was expected where the comma is in that line.

    error.jpg

    Any thoughts?

  5. #5
    It seems there are 3 open and only 2 close parentheses and I've played around with adding a 3rd but I am not getting it.

  6. #6
    Sweet, I figured it out! Seems the TARGET and RANGE were reversed.

    Private Sub Worksheet_Change(ByVal Target As Range)    Application.EnableEvents = False
         
        If Intersect(Target, Range("F:J,M:N")) Is Nothing Then Exit Sub
        Cells(Target.Row, "O") = Now
         
        Application.EnableEvents = True
    End Sub
    Thanks again SamT for getting me going in the right direction!
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False

    If Intersect(Target, Range("F:J,M:N")) Is Nothing Then Exit Sub
    Cells(Target.Row, "O") = Now

    Application.EnableEvents = True
    End Sub



  7. #7
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    the IF line is red and I get a compile error stating that a close parenthesis was expected where the comma is in that line.
    Oops! Too many Open-Parens
    If Intersect(Range("F:J,M:N"), Target)
    VBA's Error statements are not very precise. All that message means is that there is an error involving Parens somewhere on that line.

    Seems the TARGET and RANGE were reversed.
    Actually, no. Their order is not relevant. You do have the correct use of Parens. On set around the Intersect parameters, and one set around the Range Parameters.

    Noticably, extraneous sets of Parens won't break the code, as long as the total syntax is good
    If Intersect((Range("F:J,M:N")), (Target)) 'Good syntax
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  8. #8
    Ah, very good SamT, thank you for the clarification. As I said, my skills are lacking in this area.

  9. #9
    I had this working but haven't used it for a long while, now the field is not actually updating when changes are made to the designated columns.
    Is there something in Excel itself that could be stopping the modified date from populating correctly?

Posting Permissions

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