0

when I transfer the row, the error wrong data type or user-defined data type is always displayed even though the cell is defined as a date. So apparently not the date but a string is transmitted. How can I pass the string txtEnd as a date. I tried it, but it didn't work.

Thank you for your help.

Best regards

Private Sub CommandButton1_Click()
    Dim iRow As Long
    
    iRow = Tabelle1.Range("C" & Rows.Count).End(xlUp).Row + 1
    With Tabelle1
        .Range("C" & iRow).Value = Me.txtTask.Value
        .Range("D" & iRow).Value = Me.txtDescription.Value
        .Range("E" & iRow).Value = "important"
        .Range("F" & iRow).Value = "open"
        .Range("G" & iRow).Value = "not started"
        .Range("H" & iRow).Value = "not started"
        .Range("I" & iRow).Value = Me.txtBegin.Value
        .Range("J" & iRow).Value = Me.txtEnd.Value
        .Range("K" & iRow).Value = "0 %"
    End With
    
    With Me
        For Each ctl In .Controls
            If TypeName(ctl) = "TextBox" Then
                ctl.Value = vbNullString
            End If
        Next
    End With
End Sub
    
Private Sub txtEnd_AfterUpdate()
    On Error Resume Next
    Me.txtEnd = CDate(Me.txtEnd)
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
knefie
  • 95
  • 9

1 Answers1

1

If you want the value from txtEnd to be entered in column J as a date use DateValue

.Range("J" & iRow).Value = DateValue(Me.txtEnd.Value)

That should give you a date value in column J and you can format it as required.

norie
  • 9,609
  • 2
  • 11
  • 18
  • Note that in this case Excel will guess the date format and migth be wrong. If you put a `strDate` like `02/03/2020` Excel cannot know if this is meant to be `mm/dd/yyyy` or `dd/mm/yyyy` and will guess whatever the operating system is using. This might be correct or not. In case you want to be save never let Excel guess or at least validate the input before converting! Best ist to use any date-picker solution over a text box to enter a date. – Pᴇʜ Mar 25 '21 at 10:42
  • I would assume (hope) that the user would be entering the date in the correct format for their locale. Of course, a datepicker control would be great but not always available - I know I don't have the Microsoft one on this machine. – norie Mar 25 '21 at 10:45
  • 1
    Have a look here [How can I create a calendar input in VBA Excel?](https://stackoverflow.com/questions/54650417/how-can-i-create-a-calendar-input-in-vba-excel) for a pure VBA solution of a date-picker. And as a developer if you *"assume (hope)"* you are already lost. Never assume or hope always verify user input, **never** trust it. Unverified user input is one of the biggest security issues since ever computers exist. – Pᴇʜ Mar 25 '21 at 10:48