Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    automaticinsertion of date and time (2000)

    I would like to add a first column to my call log worksheet that will automatically post date and time when CSR begins to type in 2nd column of each row.

    I would also like to have the date posted automatically at the top of the form when the CSR opens their individual workbook.

    How do I do these things? Thanks as always for any help you can provide.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: automaticinsertion of date and time (2000)

    See if Legare's <post#=126713>post 126713</post#> does what you need.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: automaticinsertion of date and time (2000)

    The Code below, placed in the Worksheet Change Event Procedure should put the date and time into column A whenever something is entered in column B. It will also clear Column A if column B is cleared.

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("B:B"))
    If oCell.Value = "" Then
    ActiveSheet.Cells(oCell.Row, 1).Value = ""
    Else
    If ActiveSheet.Cells(oCell.Row, 1).Value = "" Then
    ActiveSheet.Cells(oCell.Row, 1).Value = Now()
    End If
    End If
    Next oCell
    End If
    End Sub
    </pre>


    The following code, placed in the Workbook Open Event Procedure, will place the current Date and Time into cell A1 on Worksheet Sheet1 when the workbook is opened.

    <pre>Private Sub Workbook_Open()
    Worksheets("Sheet1").Range("A1").Value = Now()
    End Sub
    </pre>

    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    Mar 2002
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automaticinsertion of date and time (2000)

    So sorry I didn't indicate I'm a real novice with Excel!

    Can you instruct me on the steps necessary to actually enter your command? I have never done this before.

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

    Re: automaticinsertion of date and time (2000)

    I'll try to explain. First, when you copy code from the Lounge, and then past it directly into Excel it usually loses all of the formatting. To get around that, open WordPad (not NotePad), then copy the code you need from the Lounge message. Paste the code into WordPad. Then copy the code just pasted into WordPad. You should now be able to paste this into Excel and have all of the formatting.

    To past the code into the WorkSheet Change Event, right click on the tab for the sheet where the code is needed. In the popup menu click on View Code. You should now have a VBE (Visual Basic Editor) window. In the upper left corner of the code window, there should be a drop down list that displays "(General)". Drop down the list and select "Worksheet" (it should be the only other choice). The drop down list in the upper right corner should now probably display "SelectionChange", and there should be Sub and End Sub statements in the code window for the Selection Change Event routine. Drop down the right hand drop down list and select "Change" from the list. You should now have Sub and End Sub statements in the code window for the Worksheet Change Event routine. Using the procedure above, you should now be able to copy the code from my previous message (without the Sub and End Sub statements) and paste it into the WorkSheet Change Event Routine.

    To find the WorkBook Open Event Routine, look in the Project Explorer on the left side of the VBE and find the ThisWorkbook object in your workbook. Right click on the ThisWorkbook object and select View Code from the popup menu. In the VBE window, select Workbook from the left drop down list. The Right drop down list will probably have Open displayed, but if it doesn't, Select Open from the right drop down list. You should now be able to paste the workbook open code into the code window as above.
    Legare Coleman

Posting Permissions

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