Userform: Combobox, dates, match required- can't get date format to work
 

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.

Userform: Combobox, dates, match required- can't get date format to work
 

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.

Userform: Combobox, dates, match required- can't get date format to work
 

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.