Dates in Userform restrictions

rhombus4

Well-known Member
Joined
May 26, 2010
Messages
563
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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


VBA Code:
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
 
Last edited by a moderator:

eirikdaude

Board Regular
Joined
Nov 26, 2013
Messages
64
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.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Nov 26, 2013
Messages
64
But a date is already stored as a number in VBA, it is only displayed slightly different
 

rhombus4

Well-known Member
Joined
May 26, 2010
Messages
563
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

VBA Code:
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
 
Last edited:

Forum statistics

Threads
1,147,743
Messages
5,742,936
Members
423,765
Latest member
PaulD1984

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top