date format

Posted by Alan on August 20, 2001 5:29 AM

I seem to have a problem with a user form I have developed.
The form is used as a purchase order interface and has a textbox in which the user inputs a date.

As I am in the UK. I need the date in the following format: dd-mm-yy .

I have formatted the destination cells in the worksheet, but regardless of this when I enter a date in the textbox, it is transferred to the destination form in American date format,; i.e. m-d-yy.

The only way I have been able to sort this out is to use letters for the month, something I am strongly trying to avoid.

I have set the UK. As the regional default in control panel, but still cant get it to play cricket instead of baseball!

Any ideas?

Thanks

Posted by Russell Hauf on August 20, 2001 12:47 PM

I guess it's kind of like explaining Cricket!

You could try something like the following. I have a form with one textbox and a command button. Pressing the command button sends the date to cell A1.

Hope this helps,

Russell

'***********************************************
Private Sub CommandButton1_Click()

If Not Mid(Me.TextBox1.Text, 3, 1) Like "[-/]" Or Not Mid(Me.TextBox1, 6, 1) Like "[-/]" Then
Me.TextBox1.Text = ""
Me.TextBox1.SetFocus
MsgBox "Date entered incorrectly - please enter again (dd-mm-yy):"
Else

Sheets(1).Range("a1").Value = _
DateSerial(Right(Me.TextBox1.Text, 2), Mid(Me.TextBox1.Text, 4, 2), _
Left(Me.TextBox1.Text, 2))
End If
Me.TextBox1.SetFocus
End Sub

'************************************************

Posted by Wiggy on August 21, 2001 1:43 AM

Cricket not Baseball

I wonder if Mr Gates knows what LBW is?

Any way to Excel, had the same problem just recently, Russell's solution will probably work, but this is simpler (I think!!)

In the userform type the following;

Private Sub UserForm_Initialize()
x = Date 'Omit if you don't want the current date
myDate = Day(x) & "-" & Month(x) & "-" & Year(x)
textbox.Value = myDate
End Sub

In this example the text box displays the current system date in UK format, if you don't want this date, just the format, omit the line of code shown.

hope this helps!

HOWZAT?!

Posted by Rob Jackson on August 21, 2001 3:36 AM

The way I get around this is by using the FormatDateTime function on the date. If you are entering a date then you can add it to the after update event.

Hope this helps.