Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
TEXTBOX - VALIDATE DATE INPUT
I want to ensure that an input to a textbox in a userform is in date format
either dd/mm/yy or dd mmm yy. Can anyone help ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
TEXTBOX - VALIDATE DATE INPUT
Try something like
Dim S As String S = InputBox("Enter A Date") If S = Format(S, "d/m/yy") Or S = Format(S, "d mmm yy") Then MsgBox "OK" Else MsgBox "Not OK" End If -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Steve_G" wrote in message ... I want to ensure that an input to a textbox in a userform is in date format either dd/mm/yy or dd mmm yy. Can anyone help ? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
TEXTBOX - VALIDATE DATE INPUT
Chip, I believe Steve was looking to validate entry in a textbox not through
a prompt. So I went to work on it, and tested this with a textbox which had 18/5/05 in it (May 18, 2005 written in the format Steve desired). I used the immediate window to test, and with these results: 1) ?Typename(textbox1.value) Ans: string (good - expected) 2) ?Format(textbox.Value, "d/m/yy") Ans: 5/5/18 (bad - not expected) Excel converted the textbox value into the wrong date because the format d/m/yy is not innate. Therefore Textbox1.value can never = Format(textbox.Value, "d/m/yy"). Do you have time to comment? Bill "Chip Pearson" wrote in message ... Try something like Dim S As String S = InputBox("Enter A Date") If S = Format(S, "d/m/yy") Or S = Format(S, "d mmm yy") Then MsgBox "OK" Else MsgBox "Not OK" End If -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Steve_G" wrote in message ... I want to ensure that an input to a textbox in a userform is in date format either dd/mm/yy or dd mmm yy. Can anyone help ? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
TEXTBOX - VALIDATE DATE INPUT
Steve
I use the BeforeUpdate event like this Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) With Me.TextBox1 If IsDate(.Text) Then .Text = Format(.Text, "dd mmm yy") Me.Label1.Caption = "" Else Cancel = True Me.Label1.Caption = "Date required" End If End With End Sub It allows the user to enter the date in any format they like, but changes it to your format. If they don't enter a valid date, it doesn't let them leave the textbox and displays a message in an adjacent label that the entry isn't valid. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com Steve_G wrote: I want to ensure that an input to a textbox in a userform is in date format either dd/mm/yy or dd mmm yy. Can anyone help ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to validate input based upon values in two other cells | Excel Discussion (Misc queries) | |||
Restrict-Filter-Limit-Validate user input in Excel | Excel Discussion (Misc queries) | |||
Validate textbox entry | Excel Programming | |||
How can I validate data input by macros? | Excel Programming | |||
Validate Textbox entry | Excel Programming |