Dates in Userform restrictions

[rhombus4]

I want to enter a value into a textbox and check to make sure it is a valid date. Then it transfer to a cell when I hit a command button

If I start to enter the date then decide I dont need it I want to be able to delete what I have entered and still be able to transfer that textbox to a cell
or even If I dont enter anything in it at all let it transfer to the cell as blank

The code below works fine to check if a date is valid or not, although If I start to enter a date and then decide I don't want to on this particular occasion and hit the backspace key/ It wont let me move on to the rest of the form. I still get the message Date is not Valid.

I did try changing the cancel = False which seemed ok but then got an error when I wanted to transfer the date to a cell
run time error 13 type mismatch think because its trying to enter a blank "" in a cell

tried both below but got error when it tried to transfer to a cell when i had cleared the textbox after I had changed the code to cancel = false
Range("A2") = CDate(Me.textbox1.Value)
Range("A2") = Format(Me.textbox1, "dd/mmm/yy").Value

Private Sub textbox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Not IsDate(Me.textbox1.Text) Then
        MsgBox "Date is not Valid"

        Cancel = True
        Exit Sub
    End If

Me.textbox1 = Format(CDate(Me.textbox1), "dd mm yy")

End Sub

 

[eirikdaude]

Handling dates in VBA is a right PITA especially if you don't use American style dates.
I have always found converting them to numbers is a lot easier & safer.

Which is why I prefer using a (relatively) robust datepicker over trying to deal with them in plain text. It removes a lot of headaches, even if it is a bit more work setting up in the first place.

 

[Fluff]

But you are still dealing with a date, that can easily get converted from dd/mm/yyyy to mm/dd/yyyy
Converting to numbers is (IMO) far simpler, easier & safer.

 

[eirikdaude]

But a date is already stored as a number in VBA, it is only displayed slightly different

 

[rhombus4]

Thanks for your help.

Yup definitely agree with regards to date formatting being a real pain

eirikdaude, for now I'll just stick to the code below which does everything I need.
Thanks for you advice and it's something I might look into if doing a bigger project

Private Sub Textbox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
   With Me.TextBox1
      If IsDate(.Text) Then
         .Value = Format(CDate(.Value), "dd mmm yy")
        
        Sheet1.Range("K4") = CLng(CDate(Me.TextBox1))
        Sheet1.Range("K4").NumberFormat = "dd mmm yy"
        
      ElseIf Len(.Value) > 0 Then
         MsgBox "Date is not valid"
         Cancel = True
         Exit Sub
      End If
   End With

End Sub