Date format from txtbox changing when submitted to data table

[inkbird1]

Hi all,
I have a userform with the date textbox as dd/mm/yyyy. I have formated the textbox value as

Private Sub UserForm_Initialize()
txtDate.Value = Format(Now(), "dd/mm/yy")
txtTime.Text = Format(Now(), "hh:mm")

When I click the add entry on my userform and go to the repository data table. The format has changed to mm/dd/yyyy despite changing the cell format back to dd/mm/yyyy

Any ideas?

 

[dmt32]

Hi,
what comes out of your textbox is text so need to coerce the content to required data type - As you are using numbers with valid date separator, DateValue function should recognize the order for day, month and year according to the short date format specified in your system.

Cells(r, 1).Value = DateValue(Me.txtDate.Value)

Be aware, that if content in your textbox is not a valid date (or empty), function will error & you should manage this.

Dave

 

[inkbird1]

Hi,
what comes out of your textbox is text so need to coerce the content to required data type - As you are using numbers with valid date separator, DateValue function should recognize the order for day, month and year according to the short date format specified in your system.

Cells(r, 1).Value = DateValue(Me.txtDate.Value)

Be aware, that if content in your textbox is not a valid date (or empty), function will error & you should manage this.

Dave

THanks Dave, where do i put the code you have provided??

 

[dmt32]

THanks Dave, where do i put the code you have provided??

I gave an example how to use DateValue function in code but if need assistance applying it to your add entry code, then please share the code here with forum & I or others will guide you.

Dave

 

[inkbird1]

Still not working - here is the code

Private Sub UserForm_Initialize()
txtTime.Text = Format(Now(), "hh:mm")
txtDate.Text = Format(Now(), "Long Date")

End Sub

Private Sub cmdADD_Click()

''''''''''''''''''Check fields for no entries'''''''''''''''''''''''''''''''''''''''''''''''''''

If txtTime.Value = "" Then
        MsgBox "Please enter a value, then try again.", vbExclamation
        Exit Sub
    End If
 
If txtDate.Value = "" Then
        MsgBox "Please enter a value, then try again.", vbExclamation
        Exit Sub
    End If

If cbo_EnteredBy.Value = "" Then
        MsgBox "Please enter a value, then try again.", vbExclamation
        Exit Sub
    End If



''''''''''''''''End Checking for blanks ''''''''''''''''''''''''''''''''''''''''''''''''''''

'dimention the variable

    Dim ws As Worksheet

    Dim addme As Range

    'set the variable

    Set ws = Sheet5

    'set variable for the destination

    Set addme = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

    'hold in memory

    Application.ScreenUpdating = False

    'send the values to the database

    With ws

        'set the date format to suit your area

        addme.Value = Format(txtDate.Value, "dd/mm/yyyy")
        addme.Offset(0, 1).Value = Me.txtTime.Value
        addme.Offset(0, 2).Value = Me.cbo_EnteredBy
        addme.Offset(0, 3).Value = Me.cboType.Value
       addme.Offset(0, 4).Value = Me.cboArea.Value
       addme.Offset(0, 5).Value = Me.txtDescription.Value
     End With

    'reset the form

    Unload Me

    'update the sheet

    Application.ScreenUpdating = True
  

End Sub
Private Sub cmdClose_Click()
frmIncidents.Hide
End Sub
Private Sub cmdReset_Click()
Unload Me
   frmIncidents.Show
End Sub

 

[inkbird1]

Worked it out


addme.Value = Cdate(txtDate.Value)