I have a combobox on a userform that pulls it's values from a named range

The named range references 10 cells formatted as dates, which contain the
formulas starting in cell A1:
=today()
=A1-1
=A2-1
etc.

When I click on the combobox, I get the dates listed in date format. When I
select one, the combobox populates with the /numeric/ date value.

I tried the following snippet, but because I require matching=true, the
combobox won't accept the changed format (invalid propery value):
Private Sub cmbDate_Change()
cmbDate.Value = Format(cmbDate.Value, "mmm-yy")
End Sub

I'm sure there must be a way to do this (Excel 97 and higher). Worst case
scenario, I suppose I can make the list text before I pull it into a named
range, but isn't there a better way? There doesn't appear to be a way to set
the format of a combobox directly....

Thanks in advance,
Keith

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

Keith,

When you load the combobox, load the text property not the value property

With cmbDate
.Additem Range("A1").Text

etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"KR" wrote in message
...
I have a combobox on a userform that pulls it's values from a named range

The named range references 10 cells formatted as dates, which contain the
formulas starting in cell A1:
=today()
=A1-1
=A2-1
etc.

When I click on the combobox, I get the dates listed in date format. When
I
select one, the combobox populates with the /numeric/ date value.

I tried the following snippet, but because I require matching=true, the
combobox won't accept the changed format (invalid propery value):
Private Sub cmbDate_Change()
cmbDate.Value = Format(cmbDate.Value, "mmm-yy")
End Sub

I'm sure there must be a way to do this (Excel 97 and higher). Worst case
scenario, I suppose I can make the list text before I pull it into a named
range, but isn't there a better way? There doesn't appear to be a way to
set
the format of a combobox directly....

Thanks in advance,
Keith

--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

Instead of linking to the named range, add the data using code

Private Sub Userform_Initialize()

for each cell in Range("Named")
cmbDate.AddItem cell.Text
Next
End Sub

Now the combobox will contain text rather than dates and will display
correctly.

--
Regards,
Tom Ogilvy

"KR" wrote in message
...
I have a combobox on a userform that pulls it's values from a named range

The named range references 10 cells formatted as dates, which contain the
formulas starting in cell A1:
=today()
=A1-1
=A2-1
etc.

When I click on the combobox, I get the dates listed in date format. When
I
select one, the combobox populates with the /numeric/ date value.

I tried the following snippet, but because I require matching=true, the
combobox won't accept the changed format (invalid propery value):
Private Sub cmbDate_Change()
cmbDate.Value = Format(cmbDate.Value, "mmm-yy")
End Sub

I'm sure there must be a way to do this (Excel 97 and higher). Worst case
scenario, I suppose I can make the list text before I pull it into a named
range, but isn't there a better way? There doesn't appear to be a way to
set
the format of a combobox directly....

Thanks in advance,
Keith

--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.