Solved: Date Formatting using a Textbox on a UserForm

[markyc]

Please help.

I have creating a userform in excel that is asking the using to enter dates of work completion, once dates have been entered by user the dates are then entered by macro into a master record.

I have got the macro working fine, but I having problems with date formats.

If user enters the date as 02-May-06 this is entered into the master OK, however if entered as 02-05-06 it is being entered into master as 05-02-06. I know this is the US date format.

Is it possible using the Textbox1_change that change the format of the date in that textbox to dd-mmm-yy.

I would like to say to users enter the date as dd-mm-yy and form or macro changes the format to dd-mmm-yy.

Any help would be excellant

Rgds

[Charlize]

I'm using variables when working with dates (living in belgium i've got the same problem - 02/05/06 becomes 05/02/06 -)

I use this in button_click on form to write to the sheet

[vba]Dim dt As Date
With Sheets("payments")
dt = Me.Payday.Value
.Cells(iRow, 3).Value = dt
End With
[/vba]
when formatting
[VBA]
Sub test_format()
Dim dt As Date
dt = InputBox("Give date - xx/mm/yyyy", "Please give date ...")
With Sheets(1)
'(dt, 1) gives long date - weekday number written month and year
.Cells(ActiveCell.Row, 3).Value = FormatDateTime(dt, 1)
End With
End Sub
[/VBA]
Charlize

[markyc]

Charlize

Thanks for your reply, setting up the variable to date has sorted out my problem, originally I had as string

I knew it would be something simple, but thanks again

Rgds

Mark

[Charlize]

Hello Mark,

Glad to be helpfull but have you read my edited post. I've added the formatting thing.

Charlize

[markyc]

Charlize

Again thanks for the extra bit of information, have now got my form and master record looking just how I wanted

Thanks

Mark