Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Todays Date shown in VBA Forms in Excel

    Hi, I have designed a VBA form in Excel and when the user clicks on the add new record button I want it to populate another text box with todays date.

    This below is my code I have used and it populates the textbox with a date but its an american style date. this is no good to me, how can I change it around for the british way?

    Private Sub cmdAdd_Click()
    lngLastRow = findLastRow(Sheets("ToSend").Range("A2"), "") + 1
    Me.cmdAdd.Enabled = False
    Me.txtInputDate = Now()
    Me.txtInputBy = fOSUserName
    End Sub

    I then tried this that had used in Access before and all it did was put it the correct way but show as text numbers.

    Private Sub cmdAdd_Click()
    lngLastRow = findLastRow(Sheets("ToSend").Range("A2"), "") + 1
    Me.cmdAdd.Enabled = False
    Me.txtInputDate = Timer.timeSince("30/12/1899", VBA.DateTime.DateValue(VBA.DateTime.DateSerial(Yea r(usrFrmCustInput.CreateDate), Month(usrFrmCustInput.CreateDate), Day(usrFrmCustInput.CreateDate))))
    Me.txtInputBy = fOSUserName
    End Sub

    Module Timer
    Function timeSince(strDateFrom As String, strDateTo As String) As Double
    If VBA.IsDate(strDateFrom) And VBA.IsDate(strDateTo) Then
    Dim lngDays As Long
    lngDays = VBA.DateDiff("d", strDateFrom, strDateTo, vbSunday)
    timeSince = lngDays
    Else
    timeSince = 0
    End If
    End Function

  2. #2
    Join Date
    Dec 2004
    Posts
    37

    Format?

    Try

    Me.txtInputDate = Format(Now(),"dd\mm\yy")

    Someone may come along wit a more elegant solution, but this should work

    Mike

  3. #3
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Thanks Mike, I tried that and this is the result in the textbox 10m1y0810

  4. #4
    Join Date
    Dec 2004
    Posts
    37

    Teach me to cut and paste rather than re-type

    Sorry,

    should be

    Me.txtInputDate = Format(Now(),"dd/mm/yy")

    Change the direction of the slashes

    Mike

  5. #5
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Teach me to read properly too :-)

    Thanks, hadn't noticed the slash

Posting Permissions

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