Combo Box on a Date Field

[Norton]

I'm creating a control Combo Box with my List Fill Range going to a range like this:

January-2002
February-2002
March-2002
April-2002

Both my dropdown and my linked cell are displaying the data as a Julian Number not a Date format. The displayed list is correct, but once you select it the selection & linked cell are wrong. I cannot find how to change my format for the dropdown selection display & linked cell. My list fill range is formated in date format. And on the linked cell changing the format there doesn't fix it.

I found it works when I use a List Box, but not a Combo Box.

Thanks in advance.

Chad


[ This Message was edited by: Norton on 2002-03-08 13:59 ]

[Russell Hauf]

On 2002-03-08 13:56, Norton wrote:
I'm creating a control Combo Box with my List Fill Range going to a range like this:

January-2002
February-2002
March-2002
April-2002

Both my dropdown and my linked cell are displaying the data as a Julian Number not a Date format. The displayed list is correct, but once you select it the selection & linked cell are wrong. I cannot find how to change my format for the dropdown selection display & linked cell. My list fill range is formated in date format. And on the linked cell changing the format there doesn't fix it.

I found it works when I use a List Box, but not a Combo Box.

Thanks in advance.

Chad


[ This Message was edited by: Norton on 2002-03-08 13:59 ]

Try this. In the change event for the combo box, put something like this:

Private Sub ComboBox1_Change()
    ComboBox1.Value = Format(ComboBox1.Text, "mmmm-yy")
End Sub

This worked for me, hope it does for you too.

-Russell

[Norton]

Thanks, it partially worked, the dropdown does display the correct format, however the linked cell still doesn't, and actually I want the linked cell to be bound to column 2 in that lookup table (a 1 for Jan, 2 for Feb, etc...) And when I do that it doesn't work again because it's trying to format a 1 as a date and getting a Runtime error Could not set value property: Invalid property attribute. All I'm getting in the linked cell is the Julian. Thanks again.



[ This Message was edited by: Norton on 2002-03-08 15:12 ]

[Russell Hauf]

On 2002-03-08 15:12, Norton wrote:
Thanks, it partially worked, the dropdown does display the correct format, however the linked cell still doesn't, and actually I want the linked cell to be bound to column 2 in that lookup table (a 1 for Jan, 2 for Feb, etc...) And when I do that it doesn't work again because it's trying to format a 1 as a date and getting a Runtime error Could not set value property: Invalid property attribute. All I'm getting in the linked cell is the Julian. Thanks again.



[ This Message was edited by: Norton on 2002-03-08 15:12 ]

Take out the linked cell reference in the properties of your ComboBox, and change the code as follows:

Private Sub ComboBox1_Change()
    ComboBox1.Value = Format(ComboBox1.Text, "mmmm-yy")
    Sheets("MySheetWithLinkedCell").Range("A1") = ComboBox1.ListIndex + 1
End Sub

Where "A1" is where you want your 1,2,3,... values.

Hope this helps,

Russell

[Norton]

Worked great, THANKS.