Input mask on a form in Excel 2003

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi My name is Lloyd
I know MS Access quite well, can you help me I want to put input mask to a
date on a field in a form in Excel eg. 2006/08/08.

Thanks
 
there is no support for this. You would need to use the change event for the
textbox to validate the entry.
 
Yes, I understand that. I assumed you mean using userform or a textbox from
the control toolbox toolbar. If not, where do you want to validate the data?

If you mean in a cell, then look at Data=>Validation
 
Yes I mean userform in a VBAProject (TextBox)

Tom Ogilvy said:
Yes, I understand that. I assumed you mean using userform or a textbox from
the control toolbox toolbar. If not, where do you want to validate the data?

If you mean in a cell, then look at Data=>Validation
Click to expand...
 
double click on the textbox in the VBE and it should take you to the change
event. Or right click on it and select view code.
 
Hi Tom ,
Myself Thulasiram..
Below code for Date input mask is working for 2007 excel but not for 2003 excel,
Do I need to modify this code to apply in 2003 excel.Kindly suggest.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim DateStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If

Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Formula)
Case 4 ' e.g., 9298 = 2-Sep-1998
DateStr = Left(.Formula, 1) & "/" & _
Mid(.Formula, 2, 1) & "/" & Right(.Formula, 2)
Case 5 ' e.g., 11298 = 12-Jan-1998 NOT 2-Nov-1998
DateStr = Left(.Formula, 1) & "/" & _
Mid(.Formula, 2, 2) & "/" & Right(.Formula, 2)
Case 6 ' e.g., 090298 = 2-Sep-1998
DateStr = Left(.Formula, 2) & "/" & _
Mid(.Formula, 3, 2) & "/" & Right(.Formula, 2)
Case 7 ' e.g., 1231998 = 23-Jan-1998 NOT 3-Dec-1998
DateStr = Left(.Formula, 1) & "/" & _
Mid(.Formula, 2, 2) & "/" & Right(.Formula, 4)
Case 8 ' e.g., 09021998 = 2-Sep-1998
DateStr = Left(.Formula, 2) & "/" & _
Mid(.Formula, 3, 2) & "/" & Right(.Formula, 4)
Case Else
Err.Raise 0
End Select
.Formula = DateValue(DateStr)
End If

End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid date."
Application.EnableEvents = True
End Sub
 
Back
Top