Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Box Dates in Userform?
How do I apply a date "mask" in an Userform textboxes. I have a lot of date
textboxes and want to make sure users do not enter anything else but date in the textbox. Thank you in advance for any help you can provide. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Box Dates in Userform?
Excel doesn't support this kind of masking.
Maybe you could use some calendar controls instead? Ron de Bruin has some notes: http://www.rondebruin.nl/calendar.htm TotallyConfused wrote: How do I apply a date "mask" in an Userform textboxes. I have a lot of date textboxes and want to make sure users do not enter anything else but date in the textbox. Thank you in advance for any help you can provide. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Box Dates in Userform?
Textboxes are strings and not dates. You must convert the string to dates and check the date. Use Datevalue to convert from string to Date the string can be in any valide date format MyDate = DateValue("February 12, 1969") You can use ISDate() function to validate that the string is a valid date. You mayu also want to add a check to make sure the date is withing a valid range do GoodDate = False MyDate = Texbox1.value if IsDate(MyDate) then 'check if date is within valid dates SDate = DateValue(MyDate) if SDate = DateValue("1/1/2000") and _ SDate = DateValue("12/31/2010") then GoodDate = True end if end if loop while GoodDate = False -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147400 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Box Dates in Userform?
You can't, but you can fake it somewhat. Here's one approach:
http://www.codeforexcelandoutlook.co...number-fields/ Technically it's not published yet, but you should still be able to view it. But you should still follow Joel's advice and use the IsDate function to verify that whatever is entered into a textbox is a valid date. --JP On Oct 23, 3:38*pm, TotallyConfused wrote: How do I apply a date "mask" in an Userform textboxes. *I have a lot of date textboxes and want to make sure users do not enter anything else but date in the textbox. *Thank you in advance for any help you can provide. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Box Dates in Userform?
Hi,
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) If Not IsDate(TextBox1.Value) Then MsgBox "Only dates allowed" TextBox1.Value = "" Cancel = True End If End Sub Mike "TotallyConfused" wrote: How do I apply a date "mask" in an Userform textboxes. I have a lot of date textboxes and want to make sure users do not enter anything else but date in the textbox. Thank you in advance for any help you can provide. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Box Dates in Userform?
Your right it is not viewable. It says it can't be found. Is there any
other way I can obtain? Thank you. "JP" wrote: You can't, but you can fake it somewhat. Here's one approach: http://www.codeforexcelandoutlook.co...number-fields/ Technically it's not published yet, but you should still be able to view it. But you should still follow Joel's advice and use the IsDate function to verify that whatever is entered into a textbox is a valid date. --JP On Oct 23, 3:38 pm, TotallyConfused wrote: How do I apply a date "mask" in an Userform textboxes. I have a lot of date textboxes and want to make sure users do not enter anything else but date in the textbox. Thank you in advance for any help you can provide. . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Box Dates in Userform?
Try the below..Textbox1 will display current date....and you can adjust....
Private Sub UserForm_Activate() Me.TextBox1 = Format(Now, "dd-mmm-yyyy") End Sub Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) 'Date and Time Picker Use down/up arrow to change date/time. Dim intPos As Integer, strType As String If KeyCode = 38 Or KeyCode = 40 Then intPos = Me.TextBox1.SelStart strType = Application.Lookup(intPos, Array(0, 3, 7), Array("d", "m", "yyyy")) Me.TextBox1 = Format(DateAdd(strType, (39 - KeyCode), TextBox1), "dd-mmm-yyyy") KeyCode = 0 Me.TextBox1.SelStart = intPos End If End Sub If this post helps click Yes --------------- Jacob Skaria "TotallyConfused" wrote: Your right it is not viewable. It says it can't be found. Is there any other way I can obtain? Thank you. "JP" wrote: You can't, but you can fake it somewhat. Here's one approach: http://www.codeforexcelandoutlook.co...number-fields/ Technically it's not published yet, but you should still be able to view it. But you should still follow Joel's advice and use the IsDate function to verify that whatever is entered into a textbox is a valid date. --JP On Oct 23, 3:38 pm, TotallyConfused wrote: How do I apply a date "mask" in an Userform textboxes. I have a lot of date textboxes and want to make sure users do not enter anything else but date in the textbox. Thank you in advance for any help you can provide. . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Box Dates in Userform?
What you do is define a constant that represents the default date
field value. Const DEFAULT_DATE_FIELD As String = "__/__/____" When the form loads, make the textbox look like a date should be entered: Private Sub UserForm_Initialize() ' assume textbox name is 'TodaysDate' Me.TodaysDate.Value = DEFAULT_DATE_FIELD End Sub Then use the Enter, Exit and AfterUpdate events to make the end user believe it's a date field. Private Sub TodaysDate_Enter() On Error GoTo ErrorHandler With Me.TodaysDate If .Value = DEFAULT_DATE_FIELD Then .Value = "" End If End With ProgramExit: Exit Sub ErrorHandler: MsgBox Err.Number & " - " & Err.Description Resume ProgramExit End Sub Private Sub TodaysDate_Exit(ByVal Cancel As MSForms.ReturnBoolean) On Error GoTo ErrorHandler With Me.TodaysDate If Len(.Value) = 0 Then .Value = DEFAULT_DATE_FIELD End If End With ProgramExit: Exit Sub ErrorHandler: MsgBox Err.Number & " - " & Err.Description Resume ProgramExit End Sub Private Sub TodaysDate_AfterUpdate() On Error GoTo ErrorHandler ' format phone number With Me.TodaysDate If Len(.Value) = 8 Then .Value = Format(.Value, "##/##/####") '.BackColor = RGB(255, 255, 255) 'Application.StatusBar = False Else .SetFocus .SelStart = 0 .SelLength = Len(.Value) ' uncomment this line (and the backcolor line above) if you want to visually indicate a data entry error '.BackColor = RGB(255, 0, 0) ' uncomment this line if you want a messagebox indicator, ' although I don't recommend interrupting the user 'MsgBox ("Phone number must be ten digits: ##########") ' uncomment this line (and the statusbar line above) if you want to use the Status bar to display error msg 'Application.StatusBar = "Phone number must be ten digits: ##########" End If End With ProgramExit: Exit Sub ErrorHandler: MsgBox Err.Number & " - " & Err.Description Resume ProgramExit End Sub On Oct 23, 5:05*pm, TotallyConfused wrote: Your right it is not viewable. *It says it can't be found. *Is there any other way I can obtain? *Thank you. "JP" wrote: You can't, but you can fake it somewhat. Here's one approach: http://www.codeforexcelandoutlook.co...textboxes-like... Technically it's not published yet, but you should still be able to view it. But you should still follow Joel's advice and use the IsDate function to verify that whatever is entered into a textbox is a valid date. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert dates from a userform | Excel Programming | |||
Insert dates from a userform | Excel Programming | |||
dates in userform | Excel Programming | |||
choosing dates on userform | Excel Programming | |||
Display dates on userform | Excel Programming |