Excel User Form - Date format

**Alice21** · 06-25-2012, 06:30 AM

Hello again

I've created an Excel User Form which requires a date to be entered (dd/mm/yy). On entering the date in to the form it switches it to show (mm/dd/yy) but when the data is added to the Excel worksheet it shows correctly as (dd/mm/yy).

Is there any way I can stop the user form showing as the american style as this may confuse the user

Many Thanks

**shaukat** · 06-25-2012, 06:36 AM

try to dim the variable as "string" not "date". What method are you using to take the input from the user?

**Alice21** · 06-25-2012, 06:44 AM

Sorry I don't follow

I have this code that I'm using:

Private Sub txtdate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With txtdate
If IsDate(.Text) Then
'/ format it
.Text = Format$(.Text, "mm/dd/yy")
End If
End With
End Sub

But if i change it to (dd/mm/yy) then when it copies over to the worksheet it changes to (mm/dd/yy), by having the code written like this when it copies over to the worksheet it shows correctly (dd/mm/yy). I'm not sure what is going on!

**smuzoen** · 06-25-2012, 06:41 AM

I assume you are using a Calendar control to enter the date? If you are then placing the value in a textbox just use

Private Sub Calendar1_Click()
TextBox1.Value = Format(Calendar1.Value, "dd/mm/yy")
End Sub

Changing the control names to whatever you are using

**smuzoen** · 06-25-2012, 06:58 AM

You are formatting the date to display that way - use something like

Private Sub txtdate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With txtdate
If Not IsDate(.Text) Then
MsgBox "You have not entered a valid date"
End If
End With
End Sub

This is why it is better to use the Calendar control as this way the date gets validated
By the way make sure you enclose your code in code tags - see the forum rules

**Alice21** · 06-25-2012, 07:02 AM

Thanks

Is there any way I can make the date show in dd/mm/yy format as the above code will allow users to enter d/m/yy and with many users accessing the form we need to ensure it looks universal for all dates.

**smuzoen** · 06-25-2012, 07:22 AM

Actually I have led you up the garden path - if you write the code above and then place it into a cell it may be in wrong format. If you use the following then users can enter 3/4/12 or 03/04/12 or 3/4/2012

Private Sub txtdate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With txtdate
If Not IsDate(.Text) Then
MsgBox "You have not entered a valid date"
Else
.Text = CDate(.Text)
End If
End With
Cells(1, 1) = CDate(txtdate.Value)
End Sub

I would really recommend you look into using a Calendar Control - I have attached a simple form showing you how to use it - this way the date is always valid. Data entry should always be validated and a Calendar Control is the best way. The only catch is not everyone may have the calendar control on their machine - if Access is installed then the Calendar Control should be. If you Right click on Toolbox and then select Additional Controls and look for Calendar Control 12 (if you are using Excel 2007) you can add the control to the toolbox. If you cannot find the calendar control or have problems running the attached workbook with a simple form I will give you instructions on how to install the calendar control - it is very simple however anyone who uses the workbook with the calendar must have the calendar control for it to work.

**Alice21** · 06-25-2012, 08:25 AM

Thanks you for this, i'll give it a go.

The only issue i have is that I can't guarantee all users will have calendar control as there are so many of them and working off different sites.

**smuzoen** · 06-25-2012, 09:10 AM

I will see if I can find you a link - there are a few calendars out there that are all VBA with no calendar control required. I like getting users to input dates via a calendar as there is no confusion and the data is validated - it saves a lot of headaches - someone else may post a link before me - even if you search the forum you will probably find one.