Solved: Date Formatting using a Textbox on a UserForm

Please help.:banghead:

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

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

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

Hello Mark,

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

Charlize

Charlize

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

Thanks

Mark:thumb