+ Reply to Thread
Results 1 to 7 of 7

Auto enter date

  1. #1
    Registered User
    Join Date
    08-30-2003
    Location
    South Africa
    Posts
    11

    Question Auto enter date

    Please refer attachment. In the header row(A), amongst other, the dates of the month appear. In the "Date Complete" column, I want the date under which the status was changed to "Passed" to automatically appear in this cell.
    Attached Files Attached Files

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Response

    You need to paste this macro onto the appropriate sheet tab in the VBA editor (e.g. Sheet1")

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 9 And Target = "Passed" And Target.Columns.Count = 1 And Target.Rows.Count = 1 Then
    Cells(Target.Row, 5) = Date
    End If
    End Sub
    Martin

  3. #3
    Registered User
    Join Date
    08-30-2003
    Location
    South Africa
    Posts
    11

    Auto enter date

    Martin,
    Appreciate your quick response. May'be I must elaborate. The workbook contains a spreadsheet for each month of the year and each spreadsheet each date for the indivudual months. The "Passed" status can appear under any of the dates for the months. The status is changed in the individual cells based on the current status for a specific date.

  4. #4
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Response

    Sorry, should have read your question properly!

    Try this

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Columns.Count = 1 And Target.Rows.Count = 1 Then
    If Target = "Passed" Then
    Sh.Cells(Target.Row, 5) = Sh.Cells(1, Target.Column)
    End If
    End If
    End Sub

    It needs to go on the ThisWorkbook tab

  5. #5
    Registered User
    Join Date
    08-30-2003
    Location
    South Africa
    Posts
    11

    Auto enter date

    Martin, can you please elaborate on the location of the macro and also if I can get it to run from a macro button?

    Thanks,
    Hennie

  6. #6
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Response

    If you open the VBA editor (ALT -F11), you will see a tab for ThisWorkbook on the left hand side.

    Paste the code into this sheet.

    There is no need for a button as the macro will run every time a cell is changed.

    On reflection you need a slightly more elaborate version to avoid problems when changing values in the first five columns

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Columns.Count = 1 And Target.Rows.Count = 1 Then
    If Target = "Passed" And Target.Column >= 6 Then
    Sh.Cells(Target.Row, 5) = Sh.Cells(1, Target.Column)
    End If
    End If
    End Sub

  7. #7
    Registered User
    Join Date
    08-30-2003
    Location
    South Africa
    Posts
    11

    Talking Auto enter date

    Matin, it works perfect!!!
    Thanks so much.

    Hennie

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1