Conflicting date formats - Excel 2007 won't recognize a date format from VBA

[potroveio]

Hi everybody,

I am struggling with a small but annoying problem here:
When I write in a sheet the date that is being entered through a VBA form ist not recognized as a value of data by Excel. It must be something related to my Excel language setting, which is in Portuguese.

In VBA tha date is formatted this way:

Private Sub cbo_data_Change()
Me.cbo_data = VBA.Format(Me.cbo_data, "dd/mm/yyyy")
End Sub

In Excel the columns are formatted to handle the date in the same way, but in portuguese, the format is: dd/mm/aaaa (a for ano, year in portuguese).
When a filter is applied in the date column, the filter is not recognizing and grouping the days into months and years.

I don't know if I related well my issue, but in any case, you can check the xls file through this link:

http://rapidshare.com/files/348267562/Despesas3.xls.html


Thanks in advance for your advice!

Martin

 

[BrianB]

Data entered into a userform is TEXT. Usually * if it is in a format that Excel recognises * it is correctly converted to a date (Date Value) when transferred to a worksheet.

I think that this is a case where Excel does not recognise it. Your code therefore needs to explicitly insert your date as a Date Value in the cell. You can then format your cells to show whatever date format you want. If you format a date cell as a normal number you will see what it really looks like.

In a userform, it is far safer and user friendly to use 3 text boxes (day month year) - or a Date Picker control.

You may be interested to see my comments about VBA dates here
http://www.mrexcel.com/forum/showthread.php?p=735659