Combo box (ActiveX) and date serial

[thart21]

Can't seem to find a similar post for my issue.

I have created an ActiveX combo box linked to another sheet to fill it.

ListFillRange: 'Combo Data'!$A$1:$B$11 which has
A B
3/1/2011 March
4/1/2011 April
5/1/2011 May
6/1/2011 June
7/1/2011 July
8/1/2011 August
9/1/2011 September
10/1/2011 October

Column A is formatted as "Date" type #3/14/01
Column B is formatted as "Text"

Everything works as it should except, everytime you close and reopen or switch tabs in the workbook, the combo box defaults to displaying the date serial for the last selection instead of the text version of the date i.e. Displaying in the combo box 40725 instead of June. I thought if I had my formatting correct in my data set, this should work.

Any ideas?

Thanks,

Toni

 

[Andrew Poulsom]

Use the ComboBox's Change event to format the date, eg:

Private Sub ComboBox1_Change()
    ComboBox1.Value = Format(ComboBox1.Value, "m/d/yy")
End Sub

 

[thart21]

Thanks, tried your suggestion and getting an invalid property error. Will this set my combo box to always display the Text version of my date or the Date format? I need it to display in the combo box the Text, i.e. June, not the mm/dd/yy formatted date.

Toni

 

[Andrew Poulsom]

To show the value in the second column when the user selects an item set the ComboBox's TextColumn to 2.

 

[thart21]

The value is working correctly when the user makes a selection, no problems there, it is strictly just when opening and looking at the combo box initially that it defaults to displaying the serial date.

My combo box data

Column A is 3/1/2011 - formatted correctly as "Date"
Column B is March - formatted correctly as "Text"

When I leave the worksheet or close the workbook and re-open it, that same combo box is showing 40765 (or whatever the serial date for March is).

I want it to actually display the word "March" at all times (or whatever the user last selected), but it keeps changing itself to the serial date.

Sorry this is so confusing, I just couldn't find any other posts that had this same issue, they all seemed to be having the problem only when a selection was made.

 

[Andrew Poulsom]

I think that's a bug, but setting the BoundColumn property to 2 fixes it.

 

[thart21]

I can't set the bound column property to 2 because it is linked to another cell that I am doing some lookups on and column 1 is my date formatted column (5/1/2011) I'll just have to accept it as a bug and let my users know that they will always see the 5 digit date initially when they open the workbook instead of May or June, etc.

Thanks for the help.

Toni