Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need A date Mask format for a Textbox on UserForm
Is there an Example on howto make a Textbox with Date Mask very like
MS Access with "__/__/____". I need its so much for my working Daily. Tks for your help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need A date Mask format for a Textbox on UserForm
You can use the Exit event to test the text of the TextBox with code like:
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim D As Date On Error Resume Next If Not (Me.TextBox1.Text Like "##/##/####") Then Cancel = True ' 'MsgBox "Invalid Date" Me.Label1.Caption = "Invalid Date" Exit Sub Else Me.Label1.Caption = "Date Is OK" End If End Sub This tests strictly for the format "##/##/####" where "#" is a numeric character. If you want to accept any valid date string, use Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim D As Date On Error Resume Next D = DateValue(Me.TextBox1.Text) If Err.Number < 0 Then Me.Label1.Caption = "Invalid Date" Cancel = True Else Me.Label1.Caption = "Date Is OK" End If End Sub I often allow for skipping validation completely, sort of a back door, by testing for the SHIFT key. Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim D As Date On Error Resume Next ' If Not (Me.TextBox1.Text Like "##/##/####") Then ' Cancel = True ' 'MsgBox "Invalid Date" ' Me.Label1.Caption = "Invalid Date" ' Exit Sub ' Else ' Me.Label1.Caption = "Date Is OK" ' End If If IsShiftKeyDown(LeftOrRightKey:=LeftKeyOrRightKey) Then Me.Label1.Caption = "Validation Skipped" Exit Sub End If D = DateValue(Me.TextBox1.Text) If Err.Number < 0 Then Me.Label1.Caption = "Invalid Date" Cancel = True Else Me.Label1.Caption = "Date Is OK" End If End Sub The IsShiftKeyDown function can be found at www.cpearson.com/Excel/KeyTest.aspx. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) wrote in message ups.com... Is there an Example on howto make a Textbox with Date Mask very like MS Access with "__/__/____". I need its so much for my working Daily. Tks for your help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need A date Mask format for a Textbox on UserForm
On Oct 8, 5:33 pm, "Chip Pearson" wrote:
You can use the Exit event to test the text of the TextBox with code like: Thank You! But this is just to text, It's not like a MASK. any idea? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Textbox date format. | Excel Discussion (Misc queries) | |||
Date format textbox | Excel Discussion (Misc queries) | |||
Userform with 5 textbox and 1 needs to refresh to have total | Excel Discussion (Misc queries) | |||
UserForm TextBox/ComboBox question | Excel Discussion (Misc queries) | |||
userform textbox | Excel Worksheet Functions |