Email:      Pass:    Pass?

Free Excel Forum

Date Format -usa To Uk In Userform

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Is there anyway of getting a userform to send the date out in UK format rather than American?

I have read many posts on it this afternoon and cant get any to work for my user form.

The database which the user form sends to cannot be changed.

Regards BVG

Similar Excel Video Tutorials

Helpful Excel Macros

Format Cells in The Short Date Number Format in Excel
- This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display
Format Cells in The Long Date Number Format in Excel
- This free Excel macro formats a selection of cells in the Long Date number format in Excel. The Long Date number format
Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Format Cells as a Scientific Number in Excel Number Formatting
- This free Excel macro formats selected cells in the Scientific number format in Excel. This means the cell will be put
Return the ISO Week Number from a Date in Excel - UDF
- Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defined function) that returns the

Similar Topics

hi evyone,
i urgently need some help!
I am having a problem with the date a on vba user form. I have searched high and low, and have come accross many threads but cant seem to fimd a solution.

Basicaly my user form reads and writes to a cell, but keeps changing the date format to us format.

please see attached file.

thanks in advance


I have a TextBox in a UserForm in which the user puts a date in any format of his choice. The routine checks that it is a recognisable date and then proceeds. In some cases I want to convert this date, in the TextBox, to the date of the last day in the month. I can do that. I can either specify the month-end date format to be used (e.g. =Format (dMonthEndDate, "dd-mmm-yyyy"), or, if I do not, I automatically get an American date ("mm-dd-yyyy") which is useless here in England.

My question is this: Is it possible, please, to pick up the date format in which the original date was entered by the user so that I can return the month-end date in the same format?


So I now have a nice little user form which will cut down data entry time by vast amounts....

Except the box for date is returning the data in the wrong format...

If I enter the date on the userform as 4/2/11
It is entered into the appropriate cell as 2/4/11 which is going to make analysis a bit difficult!

I have formatted the cells to UK date convention (dd/mm/yy) but it doesn't seem to make any difference...

The data box on the userform is a stock text box btw, not sure if this has any bearing on the situation?

Any advice much appreciated...


Can anyone offer me some help.

I have a workbook with some userforms that maintain lists/databases.

After much trail and error I have most of it working OK.

The flow is something like this.

User selects a button that opens userform 1 they input the details, the first txt box on the form is formatted to contain the date using NOW() and formatted as dd-mm-yy.

Selecting OK takes the date and the other details and pastes it as a new row to the database range on another sheet.

This works fine.

I then have another userform2 on that sheet which opens up with the records in the database. it has a next , previous and other buttons on it. My problem is that when it displays the date on the user form2 it changes the date format from dd-mm-yy to mm-dd-yy ( this is linked to the database list and it changes it here also ) if I select next to move to the next record it then changes that record also, if I then select previous the date format changes back to dd-mm-yy. This happens each time I move. There is nothing in the next or previous code to do with the date.

I have checked all the formating on each cell in the workbook and it does not seem to be linked to this.

If rather than use a userform I just select Data - Form everything works fine but I want to be able to add more options than this gives me.

Sorry if this is not explained very well.
Any help would be great.

Thank you.

I have a problem showing the date in a user form in UK format. It displays in American format, does anybody know how to change this?

I've tried te below where M66 is the date in UK format but it still shows incorrectly.


TextBox8 = Range("M66")


TextBox8 = Date

Many thanks

I have a similar problem to this thread

the basic idea is to set textbox1 in {my_}userform to 'now()' in the date format dd/mmm/yyy.
This is to speed up to filling in the user form. There is a calendar command button next to TextBox1 bringing up a separate calendar userform incase the user requires a different date, & I've successfully managed to get that to update TextBox1 no bother...

I CANNOT however get the calendar nor textbox1 to programaticaly show todays date!! {now()?}

HELP I'm stuck in a rut with this one!!
I need it to be able to copy similar initialises to other projects.

Here's what I've tried......

Option Explicit

Private Sub my_form_Initialize()
'TextBox1.text = Format(Date, "dd/mmm/yyyy")

'TextBox1.Value = Application.WorksheetFunction.Date(.Format, "dd/mmm/yyyy")

'TextBox1.Value = Date
'Date.Value = Format( Now() , "dd/mmm/yyyy")

'TextBox1.Value = Format(Date, "dd/mmm/yyyy")

end sub

all of the options that I have tried in various combinations & orders are 'commented out, NONE of them seem to work

Hi guy's,

I'm running into some trouble with my userform.

I have one sheet with dates in format dd/mm/yy (european)
These dates are used as input for a combobox in the userform.
The combobox shows the dates as they should be, no problems here.

After a date is selected, the value of the combobox is placed in a cell on my primairy sheet.
The format then is changed to the American style (mm/dd/yy). However the properties of the cell is still European. the date has changed.

For instance. 3-7-2011 = (#40609 =ok) is changed to 7-3-2011 = (#40727 =not ok)

Important for me is that the cell format must be a date, the correct date. Because formulas depend on this value, as a date.

Is there maybe a VBA code that copies the combobox value as a dd/mm/yy format?

Thanks a million

Hi, was hoping someone could help me out..

I've made a simple user form, that automatically populates the form with today's date, then when I hit my submit button, copy's that date into a cell on my workbook.

However, the date appears in the form in the UK date type, dd/mm/yyyy

but when I hit submit, and the date is copied into the cell, it appears as American, mm/dd/yyyy

I've tried setting the cells to UK date format, but this has not helped.

My code is:

'Empty Date
datetxt.Value = Date

Any help would be greatly appreciated


I am new to this forum today and i have been browsing few some old posts and found some quite userful information.

I am studying ICT at the moment and am making a project that when the user opens the excel file a userform pops up, is there a code or anything where i can just have the user form pop up? Not the back sheets in the background or anything but just the user form to open?

Also is there another such a code that stops the user from moving the userform around the screen?

If you could help me i would really appreciate it



I have User Form with one textBox which is used for entering date. Here in India standard date format is DD/MM/YYYY. After data entry is completed through this User FOrm I have to pick up some entries based on date filed. However it seems that date format in User Form is MM/DD/YYYY by default. Thus when user enters 31/3/2006 ( user intend to enter date 31st March, 2006) the same is not recognised as date field in Excel WorkSheet. Similarly when user enters 1/4/2006 (user intend to enter date 1st April, 2006), the same is considered as 4th January, 2006.

Any solution to this problem?

Please help

This is my first attempt at putting together a form. I've got an idea of what I want it to do, but I am not at all experienced with the VBA code to make it work.

Here is my user form:

I want each of the entries to go to the column of the date that corresponds with the entries.

1. The Date button will prompt a calendar for the user to select from. I think my form needs an additional text box for the date to reside in until all of the fields are filled in.

2. The value entered into each form box should then enter into the column that coincides with the date entered. What would be the VBA commands to make this happen and where should they be placed at?

3. The OK button should send all the information to their individual cells and automatically take the user to a chart in Excel that shows the data and trends that I am trying to represent. The userform shoul also close.

4. The Cancel button should take the user to the excel worksheet where they can manually manipulte the data if so desired, and close this userform.

5. The Help button will prompt a dialog box with instructions on operating the spreadsheet.

The processes don't seem too complicated as all of the calculations and analysis will occur in the worksheet. However, I am just getting my introduction into VBA, so I really don't know what goes where.

Thanks in advance.

Hi All,
I have created an VBA userform in excel which allows a user to enter data via comboboxes and textboxes etc, some of which are then passed on to several listboxes within the form. At the end of the day, when the form is complete, I want to be able to "submit" or "save" the userform as a stand alone document. Preferably in a pdf format, but it could be html, or a word doc - a format that would allow me to view the document without having to use excel. I would also like the option of being able to email the userform and have tried using an example of some code posted on the forum, however it didn't work. Any thoughts on this?


I have a userform asking the user for a date.
The info on the form then populates a spread sheet. However the date goes into the userform as text. e.g. in the form i enter 11/10/75. This populates the spreadsheet but it is populated exactly as 11/10/75 i.e. not a date, I have to actually click in the cell and press return for it to change to and be recognised as a date.

I can not figure out he code for this.

Any ideas

For reasons that are a bit longwinded to explain, I have to have the date entered in a textbox on a userform in US format. Because the Form will be used in the UK this way of entering the date is somewhat alien. How can I check that the user has input the date in the correct format, MM/DD/YY? All I want to do is open a Message box if it has been entered as DD/MM/YY.

Nice easy one for you all out there

I have a userform with several dropdown boxes on it. The Dropdowns are used to allow the user to input the time. The spreadsheet they read from is in the format "hh:mm". When the userform 'reads' them they are displayed in the form 2.3. How dod I format the dropdown boxes to show the time in 24 hr clock mode??

I have tried:

UserForm("frmDialog1").dropdown32.Format = ("hh:mm")

but get an error.

Any help appreciated.


Hello All.

The saga of my userform continues.

User is "happy"-ish with userform.. "except could you add this " and that and this..

but one issue which is a problem is the size of the userform. On my PC, both home and work, userform is fine.. on her's its huge... probably one third size bigger.. and because you can't scroll down the worksheet when the userform is open she can't access the lower section of the form.

So.. two questions, i guess.

1. can i change the settings within the VBA to re-size the userform to the user settings ( or something to allow her to see the form )

2. can the settings be changed to allow the scroll bar down the right side of the worksheet to be activated... ie so she can scroll down to see the lower sections.

thanks in advance.. and for all your help with this userform. Its been a lifesaver. Chuf

Hello Friends

I have one user form with Two Text box with spinbutton on each

i hv entered code:
#me.TextBox1.value = Date #
and same for the TextBox2 and both are formated to date by Code:
#me.TextBox1.value=Format(TextBox1.Value, "dd-mm-yy")# ' this code is entered under userform_activate
my system's short date is also set on dd-mm yy format (united kingdom style)
the SpinButtons SP1 & SP2 which is Comanded for each text box as per below code:

#Private Sub SP1_SpinDown()
Dim dt as Date
dt = Date 'I mean Current Date
dt = Format(dt, "dd-mm-yy")
If DateDiff("y", dt, me.TextBox1.Value) = 0 Then
MsgBox " This Date is Current Date, You cannot add future's Date"
me.TextBox1.Value = Datevalue(me.TextBox1.Value) + 1
End If
End Sub

Private Sub SP1_SpinUp()
me.TextBox1.Value = Datevalue(me.TextBox1.Value) - 1
End Sub

Private Sub SP2_SpinDown()
dt = Date
If DateDiff("y", dt, me.TextBox2.Value) = 0 Then
MsgBox " This Date is Current Date, You cannot add future's Date"
me.TextBox2.Text = Datevalue(me.TextBox2.Value) + 1
End If
End Sub
Private Sub SP2_SpinUp()
If DateDiff("y", STAdd.Sdate.Value, STAdd.InvDt.Value) = 0 Then
MsgBox "Transaction Date is greater then Invoice date"
me.TextBox2.Text = Datevalue(me.TextBox2.Value) - 1
End If
End Sub #

by above code i want textbox2's date should be similar or greater then the date of textbox two. in my previous verson of this userform i hv not added DateDiff cheking code, after i add DateDiff code my user form shows dates in "mm-dd-yy" format. i am facing this probel only once while i start the userform first time when i unload it n restart it the format automatically changes to dd-mm-yy

i request you to help me please with my codes, i donn mind in changing entire code or way

thanks in advance

Samir Shah

Hi all,

I created a user form and have a text box that should contain the date. When the user opens the form I'd like this cell to display as "mm/dd/yyyy". Indicating to the user that the date should be entered in that format.

Any ideas how I can accomplish this?

I have a similar request for a time field but If I can get the date, im sure i can figure out the time.

Any help would be great.

Thanks in advance.


I have a userform with 2 pages. On the second page, 2 calendar controls should appear (start date and end date). When the user goes to the 2nd page of the userform, the calendars do not appear ON the userform, but separately from it, in the upper left corner of the sheet. When selecting a date, they also appear on the form with the date selected.

I dont't know why this happens. If I create a single page userform with the 2 calendars, they appear correctly, i.e. on the form. Can anyone help me?

Thanks, Rick

I have a userform which on completion sends the relevant data to the relevant cells in excel. However I also need to send the form inputs to a person via email, in a readable format, and have no idea how to do this.

Do I need to set the layout in a word document, or can this be done in excel?

Any help would be appreciated, as would a pointing in the right direction, or to any helpful links.



I have a user form that enters data into my spreadsheet, which has been working well. However in the date field, if the user enters the date in a MM-DD-YY or similar format that enters the date fine on my worksheet, but if the user enters the date in a MMDDYY format I get a crazy date like 1/9/1924.

Is there a way to put it in the code that the date has to be entered in a certain format?

Thanks in advance for any help.

I have created a userform and now trying to ensure that users enter the correct information in each cell. There is a date field which needs to be in the format MM/YYYY. I have tried to use the below code but this seams to round up the year e.g. Jul 2009 becomes 07/2010.


Private Sub FromDate_Exit(ByVal Cancel As MSForms.ReturnBoolean) 'FromDate being the text box on the user form where the  date is entered.
    If Not IsDate(FromDate) Then
         MsgBox "Input must be a date in the  format: 'mm/yyyy'"
        Cancel = True
        FromDate = Format(FromDate, "mm/yyyy")
    End If
End Sub

Can you please help me on this.


On my user form I have a text box for the date. When I look at the data some of them have been converted into american format mm/dd/yyyy instead of dd/mm/yyyy. It only appears on some. I have a bit of code making sure that the date is filled in but this shouldn't have an effect surely?


Private Sub txtstart_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Not IsDate(txtstart) Then
         MsgBox "Input must be a date in the  format: 'dd/mm/yyyy'"
        Cancel = True
        txtFrom = Format(txtFrom, "dd/mm/yyyy")
    End If
End Sub



Good Morning, Afternoon or Evening

I'm hoping you guys maybe able to help with a small problem i'm having with Excel

I have a simple form that is used to collect data from Senior Management im using Tools from the control toolbox and have created a Form on a sheet in excel this is mirrored onto another sheet so that a user can copy and paste this into a larger database

This exercise is being done every 3months or quarterly so once the info has been input onto the main database we can get a blank form and reverse the process and get information back onto the form so that the user will only need to update..

The thing is everything is working perfectly and the process seems perfect except that when I re copy data back into the form the date format has gone and has been replaced by the excel date format i.e. 15/08/09 becomes 40040

I know this may seem like a long winded way of doing things but my limitations on excel and using data that was already here this was the best solution given the timescales..

Any help would be most appreciated


I have a userform with several drop downs where the user can select a date. The drop down list is linked to a sheet in the work book. When the date is slelected it changes to a date in the number format. Does anyone know how to prevent this.

This what I have so far many thanks.


Private Sub DayDD2_Change()
DayDD2.Value = Format "dd/mm/yyyy"
End Sub