Excel VBA Userform Date issue

Hi Adrian,

Couple of questions.

Do you want the Close Date and Time Stamp on a separate worksheet or included somewhere on the main data sheet?

Do you want a full log of each time the workbook is closed or just the last time it was closed at a single location and updated each time the workbook is closed?

The code example below produces a log of the close date and time. It goes in ThisWorkbook module. Note that after the code enters the date and time it will request the user to indicate whether to Save the workbook before closing because a change is made with the Date and Time.

The Date and time log looks like the following screen shot. I have formatted the date using the alpha abbreviation. I like this method because it leaves no doubt with the day and month.

Copy the following code and paste into ThisWorkbook module. If the worksheet does not currently exist then the code will automatically add and name the log sheet.

Do not change the sub name because it is event code that runs automatically when the workbook is closed.

If not what you want then please get back to me with more explanation.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim strSaveLog As String

    Dim wsSaveLog As Worksheet

    Dim rngDateTime As Range

    'Next Line edit "Date And Time" to preferred sheet name to save the Date and Time Stamp

    strSaveLog = "Date And Time"

    On Error Resume Next

    Set wsSaveLog = Worksheets(strSaveLog)

    On Error GoTo 0

    If wsSaveLog Is Nothing Then

        'If Date and Time log sheet not present then add the sheet.

        Set wsSaveLog = Worksheets.Add(After:=Worksheets(Sheets.Count))

        wsSaveLog.Name = strSaveLog

        With wsSaveLog

            .Range("A1") = "Date"

            .Range("B1") = "Time"

            .Range("A1:B1").Font.Bold = True

        End With

    End If

    With wsSaveLog

        'Find the next blank row in column A

        Set rngDateTime = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)

    End With

    rngDateTime.Value = Date

    rngDateTime.Offset(0, 1).Value = Time

    rngDateTime.NumberFormat = "dd mmm yyyy"  'Edit "dd mmm yyyy" to preferred date format.

    rngDateTime.Offset(0, 1).NumberFormat = "h:mm:ss AM/PM"

    wsSaveLog.Columns("A:B").AutoFit

End Sub

Hi Ozzie

That worked fine - thank you so much for everything

Adrian