date format problem

[KevHardy]

Hi,
I have a userform to record a new clients details (code shown below).

My problem is that, despite trying to make sure the date format is
dd/mm/yyyy when the data is entered on the worksheet it is in mm/dd/yyyy.

The date displays correctly on the userform and the columns are formatted a
dd/mm/yyyy.

Any ideas what's going wrong and how to fic it?

Cide:
Option Explicit

Private Sub CommandButtonCancel_Click()
Unload Me
End Sub

Private Sub CommandButtonClear_Click()
Dim ctl As Control
Me.TextDateRec.SetFocus
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub


Private Sub CommandButtonAdd_Click()
Dim iRow As Long
Dim ctl As Control
Dim ws As Worksheet
Set ws = Worksheets("Allocations")

'Check user inputs

If Me.TextName1.Value = "" Then
MsgBox "Please enter a First Name"
Me.TextName1.SetFocus
Exit Sub
End If

If Me.TextName2.Value = "" Then
MsgBox "Please enter a Surname"
Me.TextName2.SetFocus
Exit Sub
End If

If Me.TextSwift.Value = "" Then
MsgBox "Please enter a Swift Number"
Me.TextSwift.SetFocus
Exit Sub
End If

If Me.TextDateAdd = "" Then
MsgBox "Please enter a Date"
Me.TextDateAdd.SetFocus
Exit Sub
End If

If Me.TextDateRec.Value = "" Then
MsgBox "Please enter a Date"
Me.TextDateRec.SetFocus
Exit Sub
End If


If Me.TextReason.Value = "" Then
MsgBox "Please enter a Reason for Referral"
Me.TextReason.SetFocus
Exit Sub
End If

If Me.TextNeed.Value = "" Then
MsgBox "Please enter a Primary Need from the drop-down list"
Me.TextNeed.SetFocus
Exit Sub
End If

If Me.TextTime.Value = "" Then
MsgBox "Please enter a Timescale for Allocation from the drop-down list"
Me.TextTime.SetFocus
Exit Sub
End If

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

Me.TextDateAdd.SetFocus

'copy the data to the database
ws.Cells(iRow, 1).Value = TextDateAdd
ws.Cells(iRow, 2).Value = Me.TextName2.Value + ", " + Me.TextName1.Value
ws.Cells(iRow, 3).Value = Me.TextSwift.Value
ws.Cells(iRow, 4).Value = Me.TextDateRec.Value
ws.Cells(iRow, 5).Value = Me.TextReason.Value
ws.Cells(iRow, 6).Value = Me.TextNeed.Value
ws.Cells(iRow, 7).Value = Me.TextTime.Value
ws.Cells(iRow, 9).Value = ""
ws.Cells(iRow, 10).Value = ""

'clear the data
Me.TextDateAdd.Value = ""
Me.TextName1.Value = ""
Me.TextSwift.Value = ""
Me.TextDateRec.Value = ""
Me.TextReason.Value = ""
Me.TextNeed.Value = ""
Me.TextTime.Value = ""

Unload Me
MsgBox "Client has been added to the Awaiting Allocations spreadsheet"
End Sub



'Check date formats


Private Sub TextDateAdd_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If Not IsDate(TextDateAdd) Then
Cancel = True
Else

TextDateAdd = Format(TextDateAdd, "dd/mm/yyyy")
End If
End Sub

Private Sub TextDateRec_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If Not IsDate(TextDateRec) Then
Cancel = True
Else

TextDateRec = Format(TextDateRec, "dd/mm/yyyy")
End If
End Sub

 

[Dave Peterson]

I wouldn't use/trust an ambiguous format for the date--how would your program
know what the user meant when he or she entered:

01/02/03

Instead, I'd use something else to get the date (3 controls--month, day, year)
or even a calendar control.

Ron de Bruin has some notes:
http://www.rondebruin.nl/calendar.htm

But if you want, you can use cDate() to convert a string that looks like a date
to a real date -- using the local settings on the pc.

with someworksheet.range("a1")
'for testing
.numberformat = "mmmm dd, yyyy"
'when you're done testing
'.numberformat = "dd/mm/yyyy"
.value = cdate(me.textbox1.value)
end with

VBA's help for cDate() has more info.

 

[KevHardy]

I like the idea of a calendar. I have seen code for having one to enter dates
directly to an active cell, but would need to have this on the actual
userform to add dates to two text entry boxes (TextDateAdd and TextDateRec)

The code I found is:
Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "mm/dd/yyyy"
ActiveCell.Select
End Sub

Can I adapt the code to add a date in the active text box? e.i. when cursor
is in the TextDateAdd box the user clicks the calendar and the date is added.
Then when they tab through to TextDateRec and click the calendar again (this
may be a different date to the first one) a date it is added there.

Any ideas?

 

[KevHardy]

However, this doesn't answer the original query: why is the date format
changing from when it is entered in the userform to when it is added to the
worksheet?

 

[Dave Peterson]

Your code is essentially typing the characters:

01/02/2003
into a cell

Excel will look at the user's windows setting to see what order they use with
short date entries. And it'll use whatever it finds.

If you use dmy windows regional setting order, you'll be fine. If you don't,
then you won't.

 

[Dave Peterson]

I created a small userform with a calendar control (Office/Access version) and
two textboxes and a commandbutton.

This was the code behind the userform:

Option Explicit
Dim LastTB As MSForms.TextBox
Private Sub Calendar1_Click()
If LastTB Is Nothing Then
Beep
Else
LastTB.Value = Me.Calendar1.Value
End If
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub TextBox1_Enter()
Set LastTB = Me.TextBox1
End Sub
Private Sub TextBox2_Enter()
Set LastTB = Me.TextBox2
End Sub
Private Sub UserForm_Initialize()
Me.CommandButton1.Caption = "cancel"
End Sub

It keeps track of the last textbox that you clicked on.

If you want to turn it off when you click something else, you can set the lasttb
to nothing in any other _click, _enter, _change event you want.

 

[KevHardy]

I tried the 3 box idea - day, month, year but it resulted in the same problem.
The users are used to entering the date in the format dd/mm/yyyy from other
systems here. But although they enter it correctly and it displays correctly
in the user form, when it is added to the worksheet it changes to mm/dd/yyyy
despite the cell nbeing formated for a date in dd/mm/yyyy. So somehow all
this code is being superceded by something.

 

[Dave Peterson]

Check the other responses.

I tried the 3 box idea - day, month, year but it resulted in the same problem.
The users are used to entering the date in the format dd/mm/yyyy from other
systems here. But although they enter it correctly and it displays correctly
in the user form, when it is added to the worksheet it changes to mm/dd/yyyy
despite the cell nbeing formated for a date in dd/mm/yyyy. So somehow all
this code is being superceded by something.

 

[KevHardy]

I got round the problem by using a custom format for the cells of dd-mmm-yyyy
and this has worked.