VBA date problem.

[erivien]

I have a problem with a VBA form.

the code in question gets a date from a cell in the worksheet and displays it in a text box input. (theadate)
it displays dd/mm/yyyy

The user has the option to change it via a tickbox (thedate)
then they change it and then when they press the command button the code saves the new date in the cell.
it saves mm/dd/yyyy

its confusing the dates because of the inane americans!

the code looks like:

Private Sub UserForm_Initialize()
    Dim shtActive As Worksheet
    Set shtActive = Application.Workbooks("system.xls").Worksheets("stats")
    theadate = shtActive.Range("d2").Value
End Sub

Private Sub complete_Click()
    Dim shtActive As Worksheet
    Set shtActive = Application.Workbooks("system.xls").Worksheets("stats")
    If thedate = False Then
        shtActive.Range("d2").Value = theadate.Value
    Else
        shtActive.Range("d2").Formula = "=TODAY()"
    End If
    Unload fsettings
End Sub

thanks for any help

regards

 

[erivien]

woah thats alot of vba

I jus figured a way to do it, alot shorter too!

        If theadate.Text Like "##/##/####" Then
            shtActive.Range("d2").Value = DateValue(theadate.Text)
        Else
            MsgBox prompt:="Please enter a valid date format.", _
            Buttons:=vbOKOnly + vbCritical + vbDefaultButton1, _
            Title:="Alternative Date unrecognised"
        End If

 

[Andrew Poulsom]

woah thats alot of vba

I jus figured a way to do it, alot shorter too!

        If theadate.Text Like "##/##/####" Then
            shtActive.Range("d2").Value = DateValue(theadate.Text)
        Else
            MsgBox prompt:="Please enter a valid date format.", _
            Buttons:=vbOKOnly + vbCritical + vbDefaultButton1, _
            Title:="Alternative Date unrecognised"
        End If

Yes, but what if the user enters 31/02/2004. You will get 2nd March.

 

[erivien]

o yeah, i see what u mean.

thnx

 

[ashgaroth]

ye im sorri bout that :P... though i do stand by the inane comment. You do spell colour and center better but the american date is ilogical!

As the cell in question is already formatted in the dd/mm/yyyy format. It is the value that the vba is writing to it, eg (34650) that is the problem because date formatting is just displaying dd/mm/yyyy from the value...

I'll just make 3 fields makes it simpler
thanks for your help though...

Actually the English spellings are the correct ones and make much more sense. You seem to prefer a mixture as you have spelt colour the English (correct) way and center the American (incorrect) way.

 

[Fabonie243]

This worked for me:

Range("A1").Value = DateValue(theadate.Text)

The VBA DateValue function recognizes the order for month, day, and year according to the Short Date format you specified for your system.

Man you are a life saver. I'm short of word.