Combobox Format Problem

[Passman]

I am new to userforms and have a problem with the following code:

Private Sub cmdSelectDate_Click()

Sheet2.[AF1048].Value = comboSelectDate.Value
Unload Me

End Sub

Private Sub comboSelectDate_Change()

'comboSelectDate.Value = Format(comboSelectDate, "dd/mm/yy")

End Sub

What is happening is that the combobox selects the date from the range provided but when you click onto the command button it reverts to a date serial number. if I place the following code into the date change routine it shows correctly but the places text into the destination cell which the spreadsheet can't read. What I am looking for is for the date to show correctly in the combo box and be entered onto the spreadsheet as a serial date ie 39888.

I'm sure it is very simple but I can't find the answer having trawled through the forum. I would be grateful for some help.

Regards

 

[mikerickson]

Have you checked the number formatting of the destination cell?

This put an Excel Serial date in A1 and automaticaly formatted the cell to "m/d/yy" when it did so. The two testing lines in the button's Click event help show what is happening with the cell.

Private Sub cmdSelectDate_Click()
    Sheet1.Range("A1").Value = comboSelectDate.Value
    MsgBox Sheet1.Range("A1").NumberFormat
    Sheet1.Range("A1").NumberFormat = "General"
End Sub


Private Sub UserForm_Initialize()
    With comboSelectDate
        .AddItem "5/23/08"
        .AddItem "6/23/08"
        .AddItem "4/20/09"
    End With
End Sub