Combo Box Help

[DCSwearingen]

I have put a combo box from the Control Toolbar onto my spreadsheet. I
have a named range that corresponds to our company's fiscal periods
(all month ending dates, period 1 ends July 31, period 2 ends Aug 31,
etc.)

I have the named range (fPeriods) on a different spreadsheet (within
the same workbook) with the month ending dates.

The copbo box displays the list of month ending dates on the drop down,
but when a date is selected the combo box displays the numeric version
of that date.

e.g. 31-Jul-06 is the first item on the drop down list, but when it is
selected the combo control now displays --38929--

What do I need to do to have the combo box control to display in the
same format as in my list, once the list item has been selected?

 

[Dave Peterson]

One way is to populate the combobox through code.

I chose to use the workbook_open event (under the ThisWorkbook module):

Option Explicit
Private Sub Workbook_Open()
Dim myCell As Range
For Each myCell In Worksheets("Sheet2").Range("fPeriods")
Worksheets("sheet1").ComboBox1.AddItem myCell.Text
Next myCell
End Sub

(And remove the listfillrange address if you used it.)

Another way is to modify the format after the change:

Option Explicit
Dim BlkProc As Boolean
Private Sub ComboBox1_Change()
If BlkProc = True Then Exit Sub
BlkProc = True
Me.ComboBox1.Value = Format(Me.ComboBox1.Value, "mm/dd/yyyy")
BlkProc = False
End Sub

Add the listfillrange address back in this case.

 

[JimMay]

In the Combobox_Change
event code, put code like this:
ComboBox1.Value = Format(ComboBox1.Value, "mm/dd/yyyy")

 

[DCSwearingen]

I really apprecitate the solutions presented.
Multiple solutions give much food for thought!!

Thanks again!