Thread: Combobox - Date format problem

Hi,
I have a Combobox in a form (cmbDate) that looks up a list of dates from a sheet formatted (dd/mm.yy)
When I select a date it displays correctly but when I select a date from the list it then converts it to a number and saves it in that format.

Ive tried to format the combobox using the following code:

Me.cmbDate.value= Format (me.cmbDate.value, "dd/mm/yy")

 Me.cmbDate.value= Format (me.cmbDate.value, "dd/mm/yy")

I've also tried 

Me.cmbDate.date= Format (me.cmbDate.date, "dd/mm/yy")

Neither works. In first case I get error "Wrong number of arguments or invalid property assignment" with the word "Format" highlighted. In second case I get error "Method or data member not found": with the second ".Date" highlighted.

All I need to do is to be able to format the combobox field so that when it reads or writes a value it will be in the correct format.

I suspect I'm going to have the same issue with my amounts (number) fields
Regards,

Alan

Good morning AlanAnderson

What do you mean by "saves it in that format"? What are you doing with the date you select?

One fo the problems with using VBA is that you lose certain formats, such as dates, the standard Microsoft preferred format is applied. Everytime you use your selection after that, you will need to re-specify its format. The format you apply to display it in the combobox will only make it look how you want - the underlying date data will be unchanged.

HTH

DominicB

Hi,

Thanks for the response. I am looking up dates from a table (dd/mm/yy) The form is supposed to allow user to select one of the dates from the table. When user clicks on a date it is displayed on the form as a number eg 01/01/10 is displayed as 40179. I need it to display as dd/mm/yy and then, if edited, to be saved in the list as a date, not a number.

Regards,

Alan

Hi AlanAnderson

When you say "form" are you referring to a UserForm?
If so, how are you showing the date on the UserFrom - are you using a TextBox?

DominicB

Hi Dominic,

No its a standard combobox called from the toolkit.

Regards
Alan

Hi Romperstomper,

I used a line in my code stating:
me.cmbDate.rowsource="A2:A" & FinalRow

I hope that answers the question.

Regards,

Alan
BTW "Romperstomper" sounds awfully like rugrat stamper - hope not

Hi All,
Still have problem.

I have tried use of DTPicker which seems to do what I need. I just have concern about distributing programme to other machines. Will this be a problem?

Regards,

Alan