Excel VBA Userform Date issue

Hi Ozzie

That worked fine - thank you so much for everything

Adrian

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

Just an update - after some pretty rigorous testing it looks like the solution you helped with is stable and everything is working well - thank you so much!!

One last thing please

I want to try and add to the worksheet at what date and time the user closed the worksheet  or excel its self - in a similar way to the entry for saving the user form data entry

Any ideas on how to do that please?

Thanks again

Adrian

Hi Ozzie

Just an update - after some pretty rigorous testing it looks like the solution you helped with is stable and everything is working well - thank you so much!!

One last thing please

I want to try and add to the worksheet at what date and time the user closed the worksheet  or excel its self - in a similar way to the entry for saving the user form data entry

Any ideas on how to do that please?

Thanks again

Adrian

Hi Ozzie

Sorry for the delay in responding - well after a good run through of testing certainly looks like you have got to the bottom of the issue - still not sure why VBA still prefers to revert to m/d/y when the day is < 12 irrespective of the regional formatting etc -and as highlighted in the test code ( I knew about for a while which is what I tried to explain to Andreas ! - there is a video on you tube what really highlights the issue in excel and touches on the same point for UK DD/MM/YYYY issues - note now the worksheet is also correct in the formula bar after I changed the scipt to include your suggestions which then allowed me to change the .FORMAT(XXXXXXiijij), to include "dd/mm/yyyy"

Heres the link

https://www.youtube.com/watch?v=rZScXs8tfFM

Also regarding the last version I posted so you can see it working with your suggestions- still not sure where you are having the issues because of the Arabic ? the words in the script are either MSG messages which are not related - eg are you sure you want to delete? etc or lists of places or communities only which again are not critically part of any vba script and theres no english translation - if you are seeing  just strange characters or ????????? then please change the sub regional unicode setting to Arabic UAE - it doesnt affect anything else on your PC - please see my previous posting or also you may need to change the VBA language under the TOOLs tab to new courier ( Arabic) 

The above notwithstanding Ill just run through a few more examples to see if everything is running smoothly

Thank you so much!

Adrian