Date format in textbox

[Kennyatwork]

Hello everyone

I am entering the date and time into a textbox by running code from a
command button.
On clicking the button the date and time is inserted in the text box then at
a stage the value in the textbox is transfered to a cell on my worksheet.
When I view the sheet everything is fine and in the format I want.

The problem is the format in the textbox is Day and Month reversed!

I was using
TextBox1.Value = Now()
View in textBox {3/11/2004 4:18:03 PM} for March 11
When transfered to the cell , appeared as 11/3/04 16:18 {as I wanted}

So I tried
TextBox1.Value = Format(Now(),"D/M/YY HH:MM")
View in textBox {11/3/04 16:18}
When transfered to the cell , appeared as {3/11/04 16:18}

I checked my regional settings in control panel
Language - English
Region - United Kingdom..........Both as I want them!

Am I missing something, can I set the textbox to view in UK style and
transfer it to the cell still in UK format.

As there is a lot of data already in the sheet I do not want to start
messing with cell formatting.

Could anyone shed some light on this please.

Kenny
Using Me and excel 2000 &
Windows 2000 and excel 97 (strange mix but thats work for you!)

 

[Harald Staff]

Hi Kenny

It all happens in "whan transfered to the cell" which can be done in many
ways. But VBA thinks in american, so mm dd it is.

You're safest converting a string to a declared Date variable using
DateValue, it considers your regional settings. See if this get you started:

Private Sub CommandButton1_Click()
Dim D As Date
On Error Resume Next
D = DateValue(TextBox1.Text)
If InStr(TextBox1.Text, " ") < InStr(TextBox1.Text, ":") Then
D = D + TimeValue(Mid(TextBox1.Text, InStr(TextBox1.Text, " ") + 1))
End If
Range("A1").Value = D
End Sub

 

[Kennyatwork]

Harald - absolutely brilliant!

Works a treat but it has took all day for me to get it to work in my
existing project.

first part was easy enough, changing it to an "ActiveCell.Offset" reference.
For some reason I could not get it to work when I entered the code in a
module and call this up from CommandButton1_Click(). I eventually had to
put all of the module code into CommandButton1_Click() to make it work?!!!

Thanks again, it has been worth the effort

Kenny

 

[Harald Staff]

Thanks for the feedback, Kenny.
Nothing's easy, for some unexplained universal reason. "This tastes so bad
that it must be healthy", as we say here in Norway when we drink moonshine


Best wishes Harald