Results 1 to 17 of 17

Thread: Timestamp on data change

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Mar 2010
    Location
    Southeast Michigan
    Posts
    155

    Timestamp on data change

    Hello,

    I have a VBA script that is supposed to step through each cell in a range, compare it to a value in the same cell in a different sheet. If it's different then it will update the different sheet and update the time stamp. The line that errors is -

    ActiveWorkbook.Sheets("Memory").Range(cell.Address) = cell.Value

    The reference in the If statement works fine -

    If cell.Value <> ActiveWorkbook.Sheets("Memory").Range(cell.Address).Value

    I'm sure it's just me not understanding how to set a cell in another sheet?

    Here's the code I'm using :

    Code:
    Function Timestamp2(Reference As Range) As Date
        For Each cell In Reference
            If cell.Value <> ActiveWorkbook.Sheets("Memory").Range(cell.Address).Value Then
                ActiveWorkbook.Sheets("Memory").Range(cell.Address) = cell.Value
                Timestamp2 = Format(Now, "yyyy-mm-dd hh:mm:ss")
            Else
                Timestamp2 = ""
            End If
        Next cell
    End Function
    Dave

    Helpful information I've found here so far : The Definitive "Passing Data Between Forms" : Restrict TextBox to only certain characters, numeric or symbolic :
    .NET Regex Syntax (scripting) : .NET Regex Language Element : .NET Regex Class : Regular-Expressions.info
    Stuff I've learned here so far : Bing and Google are your friend. Trying to help others solve their problems is a great learning experience

  2. #2

    Thread Starter
    Addicted Member
    Join Date
    Mar 2010
    Location
    Southeast Michigan
    Posts
    155

    Re: Timestamp on data change

    Forgot to add error information. The error is

    Run-time error "1004": Application-defined or object-defined error
    Dave

    Helpful information I've found here so far : The Definitive "Passing Data Between Forms" : Restrict TextBox to only certain characters, numeric or symbolic :
    .NET Regex Syntax (scripting) : .NET Regex Language Element : .NET Regex Class : Regular-Expressions.info
    Stuff I've learned here so far : Bing and Google are your friend. Trying to help others solve their problems is a great learning experience

  3. #3
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: Timestamp on data change

    Code:
    ActiveWorkbook.Sheets("Memory").Range(cell.Address).Value = cell.Value
    Does the above work?

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Mar 2010
    Location
    Southeast Michigan
    Posts
    155

    Re: Timestamp on data change

    Same error with .Value added.
    Dave

    Helpful information I've found here so far : The Definitive "Passing Data Between Forms" : Restrict TextBox to only certain characters, numeric or symbolic :
    .NET Regex Syntax (scripting) : .NET Regex Language Element : .NET Regex Class : Regular-Expressions.info
    Stuff I've learned here so far : Bing and Google are your friend. Trying to help others solve their problems is a great learning experience

  5. #5
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Timestamp on data change

    Attached is a test workbook that does not get that error.
    Attached Files Attached Files

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Mar 2010
    Location
    Southeast Michigan
    Posts
    155

    Re: Timestamp on data change

    Well, that's weird.

    Ok, I tested my sheet by running a sub to call the function in the VBA IDE, as in your example, and everything works fine. However, if I use the function in a cell, =Timestamp2(range) , I get the errors and it fails at the same spot?
    Dave

    Helpful information I've found here so far : The Definitive "Passing Data Between Forms" : Restrict TextBox to only certain characters, numeric or symbolic :
    .NET Regex Syntax (scripting) : .NET Regex Language Element : .NET Regex Class : Regular-Expressions.info
    Stuff I've learned here so far : Bing and Google are your friend. Trying to help others solve their problems is a great learning experience

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Mar 2010
    Location
    Southeast Michigan
    Posts
    155

    Re: Timestamp on data change

    So, jdc2000, I took your workbook, moved the code to a module and ran it from the sub. Worked fine. Then added =Timestamp2(range) to a cell in the workbook and it results in the same error in your spreadsheet.

    Very strange. Possibly the way I have this set up it doesn't get write privilege to other sheets? I don't know Excel VBA well enough, obviously .
    Dave

    Helpful information I've found here so far : The Definitive "Passing Data Between Forms" : Restrict TextBox to only certain characters, numeric or symbolic :
    .NET Regex Syntax (scripting) : .NET Regex Language Element : .NET Regex Class : Regular-Expressions.info
    Stuff I've learned here so far : Bing and Google are your friend. Trying to help others solve their problems is a great learning experience

  8. #8
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Timestamp on data change

    I never try to set cell values using a Range qualifier, so I have not seen this error before.

    Will the cells you are wanting to compare always be in sheets in the same workbook?
    If yes, and you have selected a Range of cells, how do you know which other sheet you want to compare the cells to (and update those cells if they are different)?

    Are you always comparing the same range of cells, or can that range change (other than adding additional rows)?

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Mar 2010
    Location
    Southeast Michigan
    Posts
    155

    Re: Timestamp on data change

    It's supposed to loop through the cells in the range specified and compare the values to the same cells in the sheet called Memory. If the cells are different it updates them in Memory and then sets the timestamp. Although, now that we're talking it through I see I made a mistake in my logic. I should be checking all the values and updating Memory first, then, if ANY of the values in the range had to be changed, update the timestamp.

    I can rewrite that but need to understand why I'm getting my error before I can move on.

    Thanks for taking the time to look into it.
    Dave

    Helpful information I've found here so far : The Definitive "Passing Data Between Forms" : Restrict TextBox to only certain characters, numeric or symbolic :
    .NET Regex Syntax (scripting) : .NET Regex Language Element : .NET Regex Class : Regular-Expressions.info
    Stuff I've learned here so far : Bing and Google are your friend. Trying to help others solve their problems is a great learning experience

  10. #10
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Timestamp on data change

    Is the cell that gets the time stamp always the same, or always at the end of the sheet with the updated data?

    I have an idea on how to change the code so it will work with a selected range. However, when I create macros to compare data on sheets, I generally do not set up user-defined functions that get called whenever a referenced cell value changes, since I might make a typo or change the wrong cell, which might cause an error. Instead, I change the values that need to be updated, and once all data is ready for the comparison, I run the macro and compare all cells that I want to check.

  11. #11
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Timestamp on data change

    Haven't looked at the Workbook.
    1) If you want to use a VBA-Function directly in a cell on a sheet, this function must be "Public" in a standard-module.
    2) That said in 1): How are you passing "Reference As Range" to the Function? AFAIK, any Argument passed to a VBA-Function is either String or Numeric (Integer, Double).
    Error 1004 is the "El Clasico" of referencing an Object that doesn't exist, and my money is on that Reference-Range.
    So i'd look at the route of "Public Function TimeStamp2(ByVal Range As String) As Date", pass the Address as string, and then use that String to qualify your Range-Object locally.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Timestamp on data change

    how do you call the function? from some other procedure or as a userdefined worksheet function

    if you are using it as a worksheet function then you can not read or write to other ranges and have the function return a result to the calling cell

    even if from another procedure you can not have any time stamp from each cell as only the result from the last cell in the range will be returned, when the function completes
    Timestamp2 = ""
    setting a date to an empty string should cause an error, type mismatch

    i am a bit vague about what you are actually trying to achieve
    can you post a sample workbook (zip first) so we can see what it is you are wanting to do
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  13. #13

    Thread Starter
    Addicted Member
    Join Date
    Mar 2010
    Location
    Southeast Michigan
    Posts
    155

    Re: Timestamp on data change

    Thank you for the feedback. I was off work for a week and am now just getting back to this. So, attached is a sample file with the VBA code and it's use in the sheet Sheet1 cell B2. The idea is if one of the people working on this sheet changes a value in the given range it should update the date/time stamp. But, only if the value has been changed.

    Also attached is a screen grab of the VBA stepping through the code. You can see in the immediate window that ActiveWorkbook.Sheets("Memory").Range(cell.Address).Value returns the correct value, as does cell.Value, in order to make the comparison. However, the code fails on the ActiveWorkbook.Sheets("Memory").Range(cell.Address).Value = cell.Value line.

    Name:  Capture.jpg
Views: 137
Size:  29.5 KBTESTING.zip
    Dave

    Helpful information I've found here so far : The Definitive "Passing Data Between Forms" : Restrict TextBox to only certain characters, numeric or symbolic :
    .NET Regex Syntax (scripting) : .NET Regex Language Element : .NET Regex Class : Regular-Expressions.info
    Stuff I've learned here so far : Bing and Google are your friend. Trying to help others solve their problems is a great learning experience

  14. #14
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Timestamp on data change

    i am not sure i can make this work the way you are doing it, as it has several problems

    you can not write to other cells from a user defined worksheet function
    if you try to write to other cells the worksheet function will refresh again, which may possibly be resolved by setting enable events to false
    you try to check the values of all cells (d2:f2) each time

    a much better way to do this is to use the worksheet_change event
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("d2:f2")) Is Nothing Then
                If Target.Value <> ActiveWorkbook.Sheets("Memory").Range(Target.Address).Value Then
                    ActiveWorkbook.Sheets("Memory").Range(Target.Address).Value = Target.Value
                    Range("b2") = Format(Now, "yyyy-mm-dd hh:mm:ss")
                End If
    
        End If
    End Sub
    this is tested to do what i believe you want
    select the worksheet change event from the dropdowns in the code module for sheet1
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  15. #15

    Thread Starter
    Addicted Member
    Join Date
    Mar 2010
    Location
    Southeast Michigan
    Posts
    155

    Re: Timestamp on data change

    Ok, thank you Pete. I did try your process and it works perfectly. Much appreciated.

    The further I get into this the more I think my idea for tracking changes is not so sound. When I started on it I thought it was going to be straight forward and relatively simple. There are up to 100-200 rows of this data in the actual workbook and a similar logic would have to be processed on a number of additional sheets within the workbook.

    Anyone have different suggestions for accomplishing a similar concept of timestamping cell changes?
    Last edited by dlscott56; Oct 28th, 2020 at 07:43 AM.
    Dave

    Helpful information I've found here so far : The Definitive "Passing Data Between Forms" : Restrict TextBox to only certain characters, numeric or symbolic :
    .NET Regex Syntax (scripting) : .NET Regex Language Element : .NET Regex Class : Regular-Expressions.info
    Stuff I've learned here so far : Bing and Google are your friend. Trying to help others solve their problems is a great learning experience

  16. #16
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Timestamp on data change

    Anyone have different suggestions for accomplishing a similar concept of timestamping cell changes?
    extra columns next to the updated cells?

    EDIT: another thought would be to put the timestamp into the cell comments
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("d2:f2")) Is Nothing Then
                If Target.Value <> ActiveWorkbook.Sheets("Memory").Range(Target.Address).Value Then
                    With ActiveWorkbook.Sheets("Memory").Range(Target.Address)
                        .Value = Target.Value
                        If Not .Comment Is Nothing Then .Comment.Delete
                        .AddComment CStr(Now)
                    End With
                End If
    
        End If
    End Sub
    Last edited by westconn1; Oct 29th, 2020 at 04:29 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  17. #17

    Thread Starter
    Addicted Member
    Join Date
    Mar 2010
    Location
    Southeast Michigan
    Posts
    155

    Re: Timestamp on data change

    Thanks Pete. I didn't think of this. Good idea. I'm going to play around with some different options now that you've helped me. Appreciate it. I'll post what I come up with for a final solution.
    Dave

    Helpful information I've found here so far : The Definitive "Passing Data Between Forms" : Restrict TextBox to only certain characters, numeric or symbolic :
    .NET Regex Syntax (scripting) : .NET Regex Language Element : .NET Regex Class : Regular-Expressions.info
    Stuff I've learned here so far : Bing and Google are your friend. Trying to help others solve their problems is a great learning experience

Posting Permissions

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



Click Here to Expand Forum to Full Width