Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text box in userform
I have a text box in a user form that has the user put in a date. I want the text box to change the format mm/dd/yyyy in the text box if the user puts the date in like this 41304 when they tab or click to the next textbox it would format the date to this 04/13/04. I am not sure how to do this. Help would be greatly appreciated
Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text box in userform
use the change or afterupdate event of the textbo
and do this: dim MyDate as Varian MyDate = Me.Textbox_Nam if isdate(MyDate) the Me.Textbox_Name.text = format( MyDate,"mm/dd/yy" end i ----- Tim wrote: ---- I have a text box in a user form that has the user put in a date. I want the text box to change the format mm/dd/yyyy in the text box if the user puts the date in like this 41304 when they tab or click to the next textbox it would format the date to this 04/13/04. I am not sure how to do this. Help would be greatly appreciated Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text box in userform
Tim,
this needs some rules. Is 11204 11th Feb 2004, or 1st Dec 2004? Once you have the rules, it will be a simple matter of formatting with MID function etc. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Tim" wrote in message ... I have a text box in a user form that has the user put in a date. I want the text box to change the format mm/dd/yyyy in the text box if the user puts the date in like this 41304 when they tab or click to the next textbox it would format the date to this 04/13/04. I am not sure how to do this. Help would be greatly appreciated. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text box in userform
or just ues the mask property of ur textbox to control how the user inputs the date
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text box in userform
Tim,
I don't know if you mean that as a compliment or a criticism<vbg. You could check the KeyDown event for the enter key Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = 13 Then Dim MyDate As Variant MyDate = Me.TextBox1 If IsDate(MyDate) Then Me.TextBox1.Text = Format(MyDate, "mm/dd/yy") End If End If End Sub But I am confused, if you enter the date as you suggest as 41304, Chris' suggested method will not work as it will fail the IsDate test, because it isn't a date, it's a number. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Tim" wrote in message ... Bob, You always look little deeper then the rest of us. I understand what Chris was saying and that will work fine for my needs at this poont. But what I still don't underestand is how do you update the textbox after the user leaves the textbox. The only way that I can see how to do this is when the user clicks a command button. I want to do it before that. Thanks ----- Bob Phillips wrote: ----- Tim, this needs some rules. Is 11204 11th Feb 2004, or 1st Dec 2004? Once you have the rules, it will be a simple matter of formatting with MID function etc. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Tim" wrote in message ... I have a text box in a user form that has the user put in a date. I want the text box to change the format mm/dd/yyyy in the text box if the user puts the date in like this 41304 when they tab or click to the next textbox it would format the date to this 04/13/04. I am not sure how to do this. Help would be greatly appreciated. Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text box in userform
Bob I am VBA'ing
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text box in userform
Bob
I meant that as a compliment, I have read some of your posts in the past. I don't care if the textbox thinks that it a number. I just want to get the format to be 12/34/56 or if it was text ab/cd/ef. You are correct about what Chris has said when I read it I thought that it would owrk but when I cahnged it to fit in to my ccode it failed. So I am still working on it. I would think that I may have another problem in this code because I am also going to want to do this for the time, but I will conquer that when I get to that point. If I can get this date thing to work then the time thing will be easy. Thanks for the help |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text box in userform
Tim,
That was asked of Chris when he mentioned the mask property. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "TIM" wrote in message ... Bob I am VBA'ing |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text box in userform
Justr teasing Tim<G.
This is where my comment about rules came in, as I assumed the input could be 2,3,4,5, or 6 characters. If it is always 6, you could simply do Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _ ByVal Shift As Integer) If KeyCode = 13 Then Dim MyDate As Variant With Me.TextBox1 If Len(.Value) = 6 Then MyDate = Mid(.Value, 1, 2) & "/" & Mid(.Value, 3, 2) & "/" & Mid(.Value, 5, 2) If IsDate(MyDate) Then .Text = Format(CDate(MyDate), "mm/dd/yy") Else MsgBox "Input is not a date" .SelStart = 0 .SelLength = Len(.Text) .SetFocus End If Else MsgBox "Input must be 6 digits" .SelStart = 1 .SelLength = Len(.Text) .SetFocus End If End With End If End Sub It goes loopy with 29th Feb 2003 as an example though. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Tim" wrote in message ... Bob, I meant that as a compliment, I have read some of your posts in the past. I don't care if the textbox thinks that it a number. I just want to get the format to be 12/34/56 or if it was text ab/cd/ef. You are correct about what Chris has said when I read it I thought that it would owrk but when I cahnged it to fit in to my ccode it failed. So I am still working on it. I would think that I may have another problem in this code because I am also going to want to do this for the time, but I will conquer that when I get to that point. If I can get this date thing to work then the time thing will be easy. Thanks for the help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UserForm text will not wrap | Excel Discussion (Misc queries) | |||
Text box on userform | Excel Programming | |||
Userform and Text Box | Excel Programming | |||
userform text boxes | Excel Programming | |||
Userform text box | Excel Programming |