Dates as Numbers in Combo Box

ions · #1 09-08-2007, 11:42 AM

Dear Excel Expert

My combo box has a ListFillRange (Record Source) made up of Dates. When I select a date from the combo Box it converts it to a number 39325. I want the date to appear 01/09/2007 instead of 39325 but I coulnd't locat a data type property in the combo box.

Thanks so much.

I appreciate your help.

Peter.

**Leith Ross** · #2 09-08-2007, 01:03 PM

Hello Peter,

Did you mean to post an Access question in an Excel programming group?

Sincerely,
Leith Ross

**royUK** · #3 09-08-2007, 01:08 PM

That is how Excel stores dates, have you got your cells correctly formatted as dates?

**Paul** · #4 09-08-2007, 01:30 PM

To get it to show properly, you could probably set the combobox's change event to something like:

Code:

Private Sub ComboBox1_Change()
    ComboBox1.Text = Format(ComboBox1.Text, "m/d/yyyy")
End Sub

ions · #5 09-08-2007, 05:09 PM

Hi pjoaguin

thanks for your suggestion ... I tried with and without the text suffix (Me.endDate.text / Me.endDate ) but I get the following errors.

"Could not set the Text property value. Invalid property value"

"Could not set the Value property value. Invalid property value"

**Paul** · #6 09-08-2007, 06:00 PM

Hi ions,

If you are using Excel (and not Access), can you post an example of your worksheet and what you're trying to do?

I notice the "Me.endDate...", does that mean your combobox is on a userform?

Thanks.

ions · #7 09-08-2007, 09:04 PM

Hi Pjiaguin

I figured it out.... (I did it using VBA) what I originally did was place a combo box on the Worksheet... (no I didn't use a Userform I used the Me to refer to the Worksheet)

Originally, I referenced cells to act as the ListContents (Row Source) for the combo box. The cells were Formated as Dates. The reason I had them as dates was because the cells were dynamic. They were using the Date Function and I couldn't hard code them as text.

The problem was that once I selected a date in the drop down the Excel date Cell number appeared instead of the Date format.

I couldn't get around this so what I did was fill the cell contents using VBA and the cString.

Range("T1") = cString(Date - 7) This way my cells were dynamic but formated as text. Now when I select from the drop down the correct format appears in the control and not the Excel Date number.

It's just puzzling how Excel has this problem and that I had to do it Using VBA. I am surprised there isn't a way to do it without VBA

Thanks for all your advice I appreciate it. You lead me in the right direction by directing me to use VBA.

thanks pjaiguin

**royUK** · #8 09-09-2007, 03:37 AM

Using a combox with dates inputted manually shouldn't have a problem, nor do I get the same rsult when using a formula to create the dates.

JezLisle · #9 09-17-2008, 10:55 AM

I have the same problem, when I fill the Combo Box it shows as a date in the list for selecting in the Combo Box. Once I select the Date I want it then changes back t its number format. I have tried using the code below and still no joy, where am I going wrong?

Code:

Private Sub cboGetDate_Change()
    cboGetDate.Text = Format(cboGetDate.Text, "dd/mm/yyyy")
End Sub

Welcome to the Excel Forum

Forum Statistics