Data Validation in a UserForm

[PaulWJ]

I'm trying to work out how to ensure the data that's entered into a TextBox on a UserForm is actually in a date format. (dd/mm/yy). I've tried several options (which I'll list) but can't get anything to work, and I've been round a few forums for ideas. I'm self-taught, so what I've done will either have an obvious mistake, be completely wrong or, at best, very messy. I'm open to any options to correct what I've done, or if there is a better way to do it.

The UserForm is called HolidayEntry and it's purpose is to add a date to a particular list (which is selected in a ListBox on the same form)

The dd/mm/yy is shown as I've added this to the Text box on the properties box.

The date gets copied to a cell which I've defined as Input_Date, and the holiday type cell is Input_Type.

Attempt 1 was a Private Sub within the HolidayEntry code. I got no error message, it just seemed to ignore it. If I Called it in the OK Button Private Sub I got an 'Argument not optional' error (Am I supposed to put the Sub somewhere specific????)

Second attempt was to write (and by write I mean copy and adapt from a forum post) a separate Sub. First_Day is a defined cell with the date 1/1/2024 in it. Last_day has 31/12/2024 in it.

Running through this it recognises First_Day and Last_Day, but when it gets to the .Add Type line I get this error message.

I'm open to corrections or suggestions as to a better approach. If anything isn't clear, please let me know and I'll clarify.

 

[RoryA]

Your BeforeUpdate code looks fine to me (in so far as you can actually achieve what you want) - how were you testing it?

 

[RustamMika]

Hi PaulWJ.

all , what you entered into textfox is a text...
If you want date in date dormat, please use vba "converttodate" (CDate) function

 

[PaulWJ]

Your BeforeUpdate code looks fine to me (in so far as you can actually achieve what you want) - how were you testing it?

I was actually running the userform for that. I deliberately entered the date as 16/7/224, but it ran through the form and pasted the value in the cell without an issue.

 

[PaulWJ]

Hi PaulWJ.

all , what you entered into textfox is a text...
If you want date in date dormat, please use vba "converttodate" (CDate) function

Thanks. I'm not familiar with that, but will do my research. Where within either code above would that go (or replace in full) - or would it run as a separate sub?

 

[RoryA]

16/7/224 is a valid date in VBA.

If you need to restrict date entry to specific periods, you should add a test for that after testing that what was entered is an actual date. There is no way of validating whether a date was entered specifically in dd/mm/yy format (it could be in mm/dd/yy or yy/mm/dd for example). If you really insist on that level of control you'll probably need a date control, or to use separate inputs for day, month and year.

 

[PaulWJ]

16/7/224 is a valid date in VBA.

If you need to restrict date entry to specific periods, you should add a test for that after testing that what was entered is an actual date. There is no way of validating whether a date was entered specifically in dd/mm/yy format (it could be in mm/dd/yy or yy/mm/dd for example). If you really insist on that level of control you'll probably need a date control, or to use separate inputs for day, month and year.

Thanks Rory. I didn't realise that was a valid date. The second macro above was designed to try and restrict it to a date in 2024, but that keeps returning the error message.

 

[RoryA]

I'd suggest you use those dates as an additional check in the first code (data validation isn't going to work for values that you assign through a userform).