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?


"KevHardy" wrote:

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?



"Dave Peterson" wrote:

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 wrote:

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
.