Get a date to display in date format on a UserForm

[ryguy7272]

How can I get a date to display in date format on a UserForm? I thought it
would be something simple, such as the following:
If ComboBox1.Text <> "" Then
ComboBox1.Value = Format(ComboBox1.Value, "mm/dd/yyyy")
End If
However, I just see 39539 in the ComboBox when I really want to see 4/1/2008.

Thanks in advance.
Ryan--

 

[JP]

Have you tried the CDate function? Or IsDate?

If IsDate CDate(ComboBox1.Value) Then
ComboBox1.Value = CDate(ComboBox1.Value)
Else
MsgBox "Not a valid date format!"
ComboBox1.SetFocus
End If

(This is air code so step through first.)

HTH,
JP

 

[JP]

Sorry that should actually be

If IsDate CDate(ComboBox1.Value) Then
ComboBox1.Value = Format(CDate(ComboBox1.Value), "mm/dd/yyyy")
Else
MsgBox "Not a valid date format!"
ComboBox1.SetFocus
End If

 

[Peter T]

dim s
With Me.ComboBox1
If Len(.Value) Then
On Error Resume Next
s = Format(CDate(.Value), "dd-mmm-yy")
On Error GoTo 0
If .Value <> s And Len(s) Then .Value = s
End If
End With

Only change if it needs be or the combo's change even will run repeatedly;
as written above it'll fire when changed but only the once and of no
consequence.

ComboBox1.Value = Format(ComboBox1.Value, "mm/dd/yyyy")
End If
However, I just see 39539 in the ComboBox when I really want to see

4/1/2008.

I assume you really want to see 04/01/2008

Regards,
Peter T

 

[ryguy7272]

Thanks guys, but it is not working. I tried the following:
Cells(2, 13) = ComboBox1.Value
If ComboBox1.Value <> "" Then
ComboBox1.Value = Format(CDate(ComboBox1.Value), "mm/dd/yyyy")
End If

and this.............
Cells(2, 13) = ComboBox1.Text
If ComboBox1.Text <> "" Then
ComboBox1.Text = Format(CDate(ComboBox1.Text), "mm/dd/yyyy")
End If

Neither attempt worked. Any other ideas? When I click the down arrow, the
dates appear as dates. When I click on a date (select it), it is converted
into its serial format (i.e., 39539). Appreciate any help with this....

 

[Peter T]

The example I posted worked fine for me with a ComboBox in a Userform with
various values in the Combo's List.

I've tried to think of all sorts of scenarios that might give rise to what
you describe and have narrowed it down to a ComboBox on a worksheet with
it's ListFillRange linked to cells that contain dates. If(?) that's what you
have, try reformatting the cell's numberformat from default to your date
format, start with any of the built in date formats then adapt in the
Custom field as required.

Regards,
Peter T

 

[JP]

What do you mean "not working"? Does the code fail to run, if so does
it fail on a particular line? Or is the date still appearing as a
serial?

What exactly is in Cells(2,13)? Are you using this code in the module
behind a userform, or outside the userform?

If outside the userform, you would need to qualify the code I posted
by putting the form name (i.e. UserForm1) in front of the combobox
name. i.e. frmName.ComboBox1.Value instead of ComboBox1.Value.

--JP

 

[Peter T]

Seems to depend on the format, although dates are displayed in the dropdown
only a number may display when selected (ie Value). Just had another got
with no special format at all in cells, and tried the code I posted
(originally intended for a Userform) and it worked perfectly!

Did you actually try it as posted - reason for asking is the code you say
you tested is not what I suggested.

Regards,
Peter T

 

[JP]

Another thought -- maybe you should be using CDate on Cells(2,13), not
on ComboBox1.Value.

Try it that way:

If IsDate CDate(Cells(2,13).Value) Then
ComboBox1.Value = Format(CDate(Cells(2,13).Value), "mm/dd/yyyy")
Else
MsgBox "Not a valid date format!"
ComboBox1.SetFocus
End If

Also check if you need to qualify "Cells(2,13)", it isn't clear what
worksheet that is coming from.


HTH,
JP

 

[ryguy7272]

I guess I don't understand the issues involved and maybe I am not going about
this the right way. Here is my code.

Private Sub CommandButton1_Click()

Range("M2:N9").Select
Selection.Clear

Cells(2, 13) = ComboBox1.Text

If ComboBox1.Text <> "" Then
UserForm1.ComboBox1.Text = Format(CDate(ComboBox1.Text), "mm/dd/yyyy")
End If

Cells(3, 13) = ComboBox2.Text
Cells(4, 13) = ComboBox3.Text
Cells(5, 13) = ComboBox4.Text
Cells(6, 13) = ComboBox5.Text
Cells(7, 13) = ComboBox6.Text
Cells(8, 13) = ComboBox7.Text
Cells(9, 13) = ComboBox8.Text

Cells(2, 14) = TextBox1.Text
Cells(3, 14) = TextBox2.Text
Cells(4, 14) = TextBox3.Text
Cells(5, 14) = TextBox4.Text
Cells(6, 14) = TextBox5.Text
Cells(7, 14) = TextBox6.Text
Cells(8, 14) = TextBox7.Text
Cells(9, 14) = TextBox8.Text

Unload UserForm1

End Sub

Dates are in a named range and flow into the combo boxes (up to 8, but all 8
may not be used). Anyway, there can be values that correspond to each of
these dates (up to 8) and there are entered into the textboxes. I've
formatted textboxes before using this technique:

If TextBox1.Text <> "" Then
TextBox1.Text = Format(CDbl(TextBox1.Text), "#,##0.00")
End If

I guess ti is different, somehow, for dates. As it is now, when I click the
down arrow on a combobox, the dates will show up in date format, such as
4/1/2008. Then, when I select the date, 39539 is displayed in the combobox.
When I click the controlbutton to enter the value into the sheet, I see
39539, but I can just change the formatting with a few lones of code towards
the end of the macro:

Columns("M:M").Select
Selection.NumberFormat = "m/d/yyyy"
Columns("N:N").Select
Selection.NumberFormat = "#,##0.00"

Peter, I didn't try your code yet, because I didn't really know how to
implement it in the structure of my [program. I'll try it now.
Thanks for all the help!!

 

[JP]

Can you post the code that is used to populate the combo box?


Thx,
JP

 

[ryguy7272]

Eureka! I got it; it just had to be inside the sub that was identified with
the ComboBox!! Here is the solution:

Private Sub ComboBox1_Change()
If ComboBox1.Text <> "" Then
ComboBox1.Text = Format(CDate(Cells(2, 13).Text), "mm/dd/yyyy")
End If
'etc......

End Sub

Thanks for the help everyone, especially JP!! Thanks for being patient and
pushing me in the right direction!!

Cheers,
Ryan--

 

[JP]

LOL isn't that what I posted yesterday? Anyway, glad to hear it worked
out.


Thx,
JP