Excel:How change the textbox format from text to date format?

Textboxes can only contain text... much as their name implies.

Changing the format in the TextBox_change event, or any other event will still result in a String rather than a date.

If you are passing the contents of a textbox to another control (or function) that only accepts a valid date then you need to convert the contents of the textbox when passing it. For example:

Calendar.Date = cDate(TextBox1.Text)

In this situation, I usually test the contents of a textbox in the exit event and use the Format() function, but it is still just text, not a date.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    If Not IsDate(TextBox1.Text) Then
        Cancel = True
        MsgBox "Invalid date entered... Please edit.", vbExclamation
    Else
        TextBox1.Text = Format$(TextBox1.Text, "Short Date")
    End If
    
End Sub

You should also note the Exit event is not availabe for ActiveX textboxes inserted directly on a worksheet...

Many thanks for your feedback. I now understand why my calendar is not functioning.

cytop:If you are passing the contents of a textbox to another control (or function) that only accepts a valid date then you need to convert the contents of the textbox when passing it. For example:
[Select Code] copy to clipboard

Calendar.Date = cDate(TextBox1.Text)

I am passing the contetens of the textbox via a submit command button as follows:

Private Sub CommandButton2_Click()
Dim nextrow As Integer
'=COUNTA(A:A)+1
nextrow = WorksheetFunction.CountA(Sheets("Liste").Range("A:A")) + 1

Sheets("Liste").Cells(nextrow, 1) = Now
Sheets("Liste").Cells(nextrow, 2) = UserForm1.ComboBoxTestbench.Value
Sheets("Liste").Cells(nextrow, 3) = UserForm1.TextBox1.Value (calendar date "from")
Sheets("Liste").Cells(nextrow, 4) = UserForm1.TextBox2.Value (calendar date "to")
Sheets("Liste").Cells(nextrow, 5) = UserForm1.TextBox4Abt.Value
Sheets("Liste").Cells(nextrow, 6) = UserForm1.TextBox3Name.Value
Sheets("Liste").Cells(nextrow, 7) = UserForm1.TextBox6Email.Value
Sheets("Liste").Cells(nextrow, 8) = UserForm1.ComboBox2Kompo.Value
Sheets("Liste").Cells(nextrow, 9) = UserForm1.TextBox5SAP.Value
Sheets("Liste").Cells(nextrow, 10) = UserForm1.TextKurzbeschreibung.Value
Unload UserForm1
End Sub

Since I have little experiance in VBA programming I would be grateful if someone could tell me where to insert the code "Calendar.Date = cDate(TextBox1.Text" .

I have also found another possible solution

Private Sub CommandButton1_Click()
TextBox1 = Format(Date, "dd/mm/yyyy")
End Sub

But here the same question...
Thanks again