Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Durham, North Carolina, USA
    Posts
    140
    Thanks
    5
    Thanked 1 Time in 1 Post

    Update value on change (Excel XP)

    Howdy folks,

    I'm supporting 2 users who have posed me a problem. They store the date/time they entered some data in a cell (data is in column h, date/time is in column i). They're storing that date/time as "now(), but they only want the date/time to change if the value in h is changed. I have this nagging feeling there's a quick easy way to do it, but I can't think of it.

    Can someone help me out?

    Thanks,

    Beej

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

    Re: Update value on change (Excel XP)

    You can use a worksheet-level event for this. Say that the data is in cell H2, the date/time in cell I2.
    - Activate the Visual Basic Editor (Alt+F11)
    - Double click the object corresponding to the relevant worksheet in the Project Explorer (the Windows Explorer-like treeview pane).
    - Enter or copy the following code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("H2")) Is Nothing Then
    ' Cell H2 changed, so set cell I2
    Range("I2").Value = Now
    End If
    End Sub

    - Switch back to Excel (Alt+F11) to test.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Durham, North Carolina, USA
    Posts
    140
    Thanks
    5
    Thanked 1 Time in 1 Post

    Re: Update value on change (Excel XP)

    Hans,

    Thanks for the quick answer--it worked like a champ, as I knew it would. But I left out a vital piece of info--they're doing this for multiple rows. In other words, it's not just h2 and i2, it's h3/i3, h4/i4, etc. They've told me there will be a maximum # of rows, so I could just copy/paste your code and update to reflect each row change, but is there a way through code? Maybe a For Each...Next?

    Thank you again.

    Beej

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

    Re: Update value on change (Excel XP)

    Try this variation:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    If Not Intersect(Target, Range("H:H")) Is Nothing Then
    For Each c In Intersect(Target, Range("H:H"))
    c.Offset(0, 1).Value = Now
    Next c
    End If
    Set c = Nothing
    End Sub

    You can replace "H:H" by "H1:H100" or whatever suits your purpose.

  5. #5
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update value on change (Excel XP)

    I have a similar situation to the one posed in this thread but the only difference is that I need to kick of my code if someone does something as simple as sorting the data with no data entry change. Can this be done? I can't seem to trap the sort as an action that will kick off the code. Please help!!

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Update value on change (Excel XP)

    I do not think sort triggers any event by itself. After a sort the change event does not even trigger!

    One thought (though not worked out completely, so test it more thouroughly for false positives and negatives)

    [Change the names as appropriate]
    Assume sheet1 has the table to sort. and the data starts in cell A2
    Assume sheet2 is an unused (could be hidden) sheet

    In sheet2 cell A1 enter the formula:
    <pre>=Sheet1!A2</pre>


    In the thisworkbook object add the code:
    <pre>Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    If Sh.Name = "Sheet2" Then MsgBox "Sorted"
    End Sub</pre>


    It will trigger on a sort. Unfortunately, it will also trigger on a change to the cell A2. I can see no way around this, since the calc event occurs before the change event.

    Perhaps someone else will get a better idea.

    Steve

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

    Re: Update value on change (Excel XP)

    Just sorting a range in itself will trigger no worksheet or workbook event, as far as I know. But if there is a formula that refers to the range being sorted, the Worksheet_Calculate event occurs (and also the application level SheetCalculate event). You might be able to use that. Otherwise, the only workaround I can think of is to provide your own sort buttons. You can control what happens in the code bhind those buttons.

  8. #8
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update value on change (Excel XP)

    Using the Worksheet_Calculate event was able to kick off my code but then it went into a horrible loop as each time the code ran it would trigger the event all over again. Any thoughts?

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Update value on change (Excel XP)

    Does my method work for you?

    Steve

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

    Re: Update value on change (Excel XP)

    You can set Application.EnableEvents = False at the beginning of the event procedure, and Application.EnableEvents = True at the end, to prevent other events occurring while you're handling one.

  11. #11
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update value on change (Excel XP)

    Steve & Hans,

    I decided to utilize Hans suggestions because I knew the way the code was working and more importantly the approach I was following, I was on the last mile. Hans, your last suggestion enabled me to isolate the the event based on the action and prevent that endless loop from happening. Thank you both for your efforts!!

Posting Permissions

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