Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a date to display in date format on a UserForm
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-- -- RyGuy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a date to display in date format on a UserForm
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 On Mar 21, 11:08*am, ryguy7272 wrote: 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-- -- RyGuy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a date to display in date format on a UserForm
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 On Mar 21, 11:46*am, JP wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a date to display in date format on a UserForm
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" wrote in message ... 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-- -- RyGuy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a date to display in date format on a UserForm
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.... -- RyGuy "Peter T" wrote: 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" wrote in message ... 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-- -- RyGuy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a date to display in date format on a UserForm
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 "ryguy7272" wrote in message ... 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.... -- RyGuy "Peter T" wrote: 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" wrote in message ... 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-- -- RyGuy |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a date to display in date format on a UserForm
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 On Mar 21, 12:46*pm, ryguy7272 wrote: 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.... -- RyGuy "Peter T" wrote: 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" wrote in message ... 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-- -- RyGuy- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a date to display in date format on a UserForm
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 "Peter T" <peter_t@discussions wrote in message ... 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 "ryguy7272" wrote in message ... 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.... -- RyGuy "Peter T" wrote: 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" wrote in message ... 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-- -- RyGuy |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a date to display in date format on a UserForm
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 On Mar 21, 12:46*pm, ryguy7272 wrote: 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.... -- RyGuy |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a date to display in date format on a UserForm
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!! -- RyGuy "JP" wrote: 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 On Mar 21, 12:46 pm, ryguy7272 wrote: 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.... -- RyGuy "Peter T" wrote: 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" wrote in message ... 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-- -- RyGuy- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a date to display in date format on a UserForm
Can you post the code that is used to populate the combo box?
Thx, JP On Mar 21, 2:15*pm, ryguy7272 wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a date to display in date format on a UserForm
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-- -- RyGuy "JP" wrote: 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 On Mar 21, 12:46 pm, ryguy7272 wrote: 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.... -- RyGuy |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a date to display in date format on a UserForm
LOL isn't that what I posted yesterday? Anyway, glad to hear it worked
out. Thx, JP On Mar 22, 4:02*pm, ryguy7272 wrote: 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-- -- RyGuy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Format in a Userform Listbox | Excel Programming | |||
Date Value in wrong format on userform | Excel Programming | |||
Date Format in Userform | Excel Programming | |||
Date format in userform | Excel Programming | |||
Date format from UserForm | Excel Programming |