Userform Date Validation 1

[advox1]

A date is input into a userform and when transferred to the spreadsheet it americanises.

This is regardless of cell validation and regional settings.

Can anyone advise a solution to ensure the date enters in the UK way?

Thanks

 

[Bowers74]

I'm not exactly sure what you are trying to explain, but I think I have a good idea.

I am assuming that your line of code that transfers the date to the spreadsheet looks something like this:

worksheets("Sheet1&quot.Range("A1&quot.value = Textbox1.Value

If you want the date to be returned in a specific value, first set the cell's format to general again because it has been changed and then change your line of code to something like this:

Sheet1.[A1] = Format(TextBox1, "ddmmyyyy&quot

I'm not sure what the UK date format looks like, but I think that is it. If not then just change the format to fit your needs.

If you want to see why my sheet and cell references look different than the typical VBA sheet and cell references, take a look at my FAQ:

faq707-4090 - Refer to Worksheets more effecively in a Procedure

Let me know if this helps!



Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[advox1]

Thanks Mike

However I am using the following line of code to transfer as the info has to go in the next empty line as defined by

nextrow = Application.WorksheetFunction.CountA(Range("A:A&quot) + 1


Cells(nextrow, 1) = DateBox.Text

There are then approx 10 other items which are transferred.

The problem is that when entering the date in the userform as 03/01/04 which is the UK format it will change the format to 01/03/04 every time the data is transferred.

Joanne

 

[Bowers74]

First of all, I would like to simplify your nextrow variable setting:

nextrow = [A65536].end(xlup).row + 1

Next, your DateBox line should then read:

Cells(nextrow, 1) = DateSerial(Year(DateBox), Month(DateBox), Day(DateBox))

This will ensure that the serial number for the date is transfered and not the text. If the cell's format has been set to dd/mm/yy then there won't be a problem and if it hasn't, then Excel will format it automatically for you. If you want to send the serial number as a pre-formatted date then use:

 Cells(nextrow, 1) = Format(DateSerial(Year(DateBox), Month(DateBox), Day(DateBox)), "dd/mm/yy")

I hope this helps!




Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[TonyJollans]

Hi advox1,

There isn't any (easy) way of controlling the format of dates input in textboxes - they are interpreted as US style so you would have a problem trying to do it with a linked cell, but in VBA code you can override the default by using functions which interpret according to your Regional Settings. Mike's DateSerial should work, but using CDate is probably easier:

Cells(nextrow, 1) = CDate(DateBox)

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[advox1]

Thanks Tony

This has sorted the problem.

 

[PeterWallace]

One small problem with CDATE


works fine on a m/c where the regional settings have been set to the format you want .... but where ( lazy User ) has not changed regional setting .... you will get the default ... usually American format