Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Mar 2002
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto TimeStamp (97 SR2)

    Looking for an event macro that will take the following items
    current worksheet
    current row & column
    and upon any cell in column E being changed write a static (non-changing) NOW() stamp into column Z on the same row as the change was made.

    Seems simple enough but (a) I am not sure what event to use, and ([img]/forums/images/smilies/cool.gif[/img] I am not sure how to tell the macro the current row & column. This spreadsheet has 60+ worksheets so I would prefer to do this at the workbook level.

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

    Re: Auto TimeStamp (97 SR2)

    This code in the worksheet change event routine should do what you want:

    <pre>Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
    For Each oCell In Target
    If Not Intersect(oCell, Range("E:E")) Is Nothing Then
    Sh.Cells(oCell.Row, 26).Value = Now()
    End If
    Next oCell
    End If
    End Sub
    </pre>

    Legare Coleman

  3. #3
    Lounger
    Join Date
    Mar 2002
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto TimeStamp (97 SR2)

    Hi Legare:

    This "timestamp" function works great, until I invoked ShowDataForm for data entry. Is there any way to tie this function to the DataForm process?

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

    Re: Auto TimeStamp (97 SR2)

    You could put a button on the form and have the click event for the button do the same thing the function does and put the result where you want it on the form.
    Legare Coleman

  5. #5
    Lounger
    Join Date
    Mar 2002
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto TimeStamp (97 SR2)

    Yes, that is essentially what I am doing now -- invoking a macro that calls ShowDataForm which is then followed by some general processes on the whole sheet when I close the form.

    But how can I make the macro place the timestamp in the same row as the new data record(s)?

    One thought would be to scan Column Z for empty cells and fill them with a stamp after the form closes, but I would prefer something more 1-to-1 event wise.

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

    Re: Auto TimeStamp (97 SR2)

    How are you moving all of the other values from the form to the worksheet? Couldn't the macro put the date stamp in a control on the form, then the same method that moves the other data could move it also.
    Legare Coleman

  7. #7
    Lounger
    Join Date
    Mar 2002
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto TimeStamp (97 SR2)

    I am using the *default* DataForm (VBA command "ActiveSheet.ShowDataForm", a.k.a. menu item "Data" - "Form..." (I just click the New button and fill in the blanks).

    I'm afraid I have never quite understood how to make custom forms for data-entry purposes. I'd love to learn - any good tutorials or books on this particular subject?

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

    Re: Auto TimeStamp (97 SR2)

    OK, then the code below will put a date/time stamp in column A in the last row which has something in column B. Does that do what you need?

    <pre>Dim lLastRow As Long
    lLastRow = Worksheets("Sheet1").Range("B1").Offset(Worksheets ("Sheet1").UsedRange.Rows.Count, 0).End(xlUp).Row
    Worksheets("Sheet1").Range("A1").Offset(lLastRow - 1, 0).Value = Now()
    </pre>

    Legare Coleman

  9. #9
    Lounger
    Join Date
    Mar 2002
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto TimeStamp (97 SR2)

    I thought I had already posted a reply but its not here so I'll try again...

    The answer to your "How are you..." question is that I am using the built-in data entry DataForm (menu "Data" - "Form..." or VBA ActiveWorksheet.ShowDataForm). The form comes up, the user clicks NEW and fills in the blanks. Done.

    I have no experience with how to make custom user forms do what I want but if you can route me to a good tutorial on how to make them really interactive I would love to try. What would be really cool would be a tutorial/sample workbook that shows how to make **the** DataForm just like Excel has built in.

Posting Permissions

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