VBA Input Box with date validation

[Legacy 61262]

how can i show an input box where the user enters a date and will only accept this date if it is entered in a particular format. ie if they enter as

dd/mm/yyyy

thanks
_________________
king

 

[Legacy 98055]

You may validate after the user has entered the date. If you are looking for masking, don't bother using an input box...

<table border="1" bgcolor="White"><caption ALIGN=left><font size="2" face=Courier New>Example VBA Code:</FONT></caption><tr><td><font size="2" face=Courier New>  <font color="#0000A0">Sub</font> Example()
       <font color="#0000A0">Dim</font> Answer <font color="#0000A0">As</font> <font color="#0000A0">String</font>
      
  ReTry:
       Answer = InputBox("Please enter the date in the following format: dd/mm/yyyy", "Enter Date", Format(Date, "dd/mm/yyyy"))
       <font color="#0000A0">If</font> Answer = "" <font color="#0000A0">Then</font> <font color="#0000A0">Exit</font> <font color="#0000A0">Sub</font>
      <font color="#008000"> 'valid dates will be 1900 to 2099. Edit to extend the range</font>
       <font color="#0000A0">If</font> <font color="#0000A0">Not</font> IsDate(Answer) <font color="#0000A0">Or</font> <font color="#0000A0">Not</font> Answer <font color="#0000A0">Like</font> "[0-3]#/[01]#/[12][09]##" <font color="#0000A0">Then</font>
           <font color="#0000A0">If</font> MsgBox("Invalid date or invalid date format. " & _
               "Please enter the date in the correct format.", vbRetryCancel) = vbRetry <font color="#0000A0">Then</font>
               <font color="#0000A0">GoTo</font> ReTry
           Else: <font color="#0000A0">Exit</font> <font color="#0000A0">Sub</font>
       <font color="#0000A0">End</font> <font color="#0000A0">If</font>
      
       <font color="#0000A0">End</font> <font color="#0000A0">If</font>
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table>

 

[Legacy 61262]

Masking?

Thanks Tom thats great as it works but what do you mean by MASKING? I havent come across that before?

 

[Legacy 98055]

Masking would validate the input as it is typed. For example, to mask your date, you might see something like this:

__/__/____

or

dd/mm/yyyy

or

##/##/####

 

[Legacy 61262]

Masking How?

So how do I go about masking?
Thanks for your assistance with this..

 

[Legacy 98055]

The most common way is using a control such as MS' Masked Edit control. Or you can use a standard textbox control with some very tedious code...

 

[Legacy 61262]

Masking How?

sorry but what is ms masked edit control or textbox control? I havent come across eother of them?

 

[Norie]

King

Why do you need the user to enter the date in a particular format?

Couldn't you just check that the entry is a date and then format as required?

That would allow for more flexibility.

 

[Sank777]

Hi Tom, in there anyway we can change date format to "DD-MMM-YYYY" ?

Thanks...

 

[Sank777]

hi tom, below alterations in your code helped me....

Dim Answer As String


ReTry:
Answer = InputBox("Please enter the date in the following format: dd-mm-yyyy", "Enter Date", Format(Date, "dd-mmm-yyyy"))


If Answer = "" Then Exit Sub
Answer = Format(Answer, "DD-MM-YYYY")
'valid dates will be 1900 to 2099. Edit to extend the range
If Not IsDate(Answer) Or Not Answer Like "[0-3]#-[01]#-[12][09]##" Then
If MsgBox("Invalid date or invalid date format. " & _
"Please enter the date in the correct format.", vbRetryCancel) = vbRetry Then
GoTo ReTry
Else
Answer = Format(Answer, "DD-MMM-YYYY")
Exit Sub
End If
End If