Formatting of dates in user forms

  • Hello
    So, I have created a user form to enter data onto a spreadsheet. The problem is....(Please see code below)when the form opens it automatically puts today's date in the "txtDate" box and when the enter key is pressed it loads the date in the desired format onto the spreadsheet. The desired format is dd/mm/yyyy.

    If I manually change the date in the user form it reverts back and enters on the sheet as mm/dd/yyyy

    However I don't want to use the "Today()" what I have been trying (many, many ways) is to leave the date box clear on the user form and manually enter the date (dd/mm/yyyy) in the user form and to, when the enter button is pressed to have it transferred to the spreadsheet in the same format.

    It is the last three lines that is giving me a hair pulling problem (I am now bald).

    To learn should be desired, to help is inspiring.
    Please 'inspire' me and quench a little of my 'desire'.

  • Re: Formatting of dates in user forms

    Thanks for the speedy response.
    I did as you suggested (unless Mr stupid here has misinterpreted your instruction)

    1. Deleted the following:

    Private Sub UserForm_Initialize() 
        txtDate.Text = Format(Now(), "dd/mm/yyyy") 
    End Sub

    2. The changed:

    ws.Cells(newRow, 2).Value = Me.txtDate.Value


    ws.Cells(newRow, 2).Value = Date

    I can enter the date in the user form as I want but on the click to enter the data onto the spreadsheet today's date is entered
    It can only be me, please tell me where I've gone wrong.

  • Re: Formatting of dates in user forms

    I misunderstood, I thought you wanted today's date all the time on the spreadsheet.

    Just wrap your existing code with a Format() method too, I'd also cast the value to a Date because you're working with a textbox (which returns a string):

    ws.Cells(newRow, 2).Value = Format(CDate(Me.txtDate.Value), "dd/mm/yyyy")

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!