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?


--
DCSwearingen

Getting old, but love computers.
------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=547249

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.

DCSwearingen wrote:

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?

--
DCSwearingen

Getting old, but love computers.
------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=547249

--

Dave Peterson

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

"DCSwearingen"
wrote in
message news:DCSwearingen.28oycb_1149111301.6721@excelforu m-nospam.com:

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?


--
DCSwearingen

Getting old, but love computers.
------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=547249

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

Thanks again!


--
DCSwearingen

Getting old, but love computers.
------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=547249