Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox validation code
In my userform I have 2 TextBoxes.
I nedd code for validation of TextBox2 is entry : it should be Date,should be current month and current year date,entry date should be greater than or equal to textbox1. Thank You. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200905/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox validation code
Try the following. Let me know if not working as you want it to but let me
know what locale date format you are using. Private Sub TextBox2_AfterUpdate() Dim bolValid As Boolean Dim dateValid As Date On Error Resume Next dateValid = DateValue(Me.TextBox2) On Error GoTo 0 If dateValid 0 Then bolValid = True Else bolValid = False MsgBox "Invalid date format in TextBox2" _ & vbCrLf & "Must be d/m/y format" Exit Sub End If If Year(dateValid) = Year(Date) Then bolValid = True Else bolValid = False MsgBox "Invalid Year in TextBox2" & _ vbCrLf & "Must be " & Year(Date) Exit Sub End If If Month(dateValid) = Month(Date) Then bolValid = True Else bolValid = False MsgBox "Invalid Month in TextBox2" & _ vbCrLf & "Must be " & Month(Date) Exit Sub End If If dateValid = DateValue(Me.TextBox1) Then bolValid = True Else bolValid = False MsgBox "Invalid date in TextBox2" & _ vbCrLf & "Must be = to TextBox1" Exit Sub Exit Sub End If 'You should not need following code 'Only there for testing If bolValid Then MsgBox "Date in TextBox2 is valid" End If End Sub -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox validation code
I have forgotten to mention,If validation does not meet any condition cursor
should go TextBox2 prmpt for correct entry. I am using dd/mm/yy date format OssieMac wrote: Try the following. Let me know if not working as you want it to but let me know what locale date format you are using. Private Sub TextBox2_AfterUpdate() Dim bolValid As Boolean Dim dateValid As Date On Error Resume Next dateValid = DateValue(Me.TextBox2) On Error GoTo 0 If dateValid 0 Then bolValid = True Else bolValid = False MsgBox "Invalid date format in TextBox2" _ & vbCrLf & "Must be d/m/y format" Exit Sub End If If Year(dateValid) = Year(Date) Then bolValid = True Else bolValid = False MsgBox "Invalid Year in TextBox2" & _ vbCrLf & "Must be " & Year(Date) Exit Sub End If If Month(dateValid) = Month(Date) Then bolValid = True Else bolValid = False MsgBox "Invalid Month in TextBox2" & _ vbCrLf & "Must be " & Month(Date) Exit Sub End If If dateValid = DateValue(Me.TextBox1) Then bolValid = True Else bolValid = False MsgBox "Invalid date in TextBox2" & _ vbCrLf & "Must be = to TextBox1" Exit Sub Exit Sub End If 'You should not need following code 'Only there for testing If bolValid Then MsgBox "Date in TextBox2 is valid" End If End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200905/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox validation code
Can only get the Cursor to set the focus back to TextBox2 if done in the Exit
Event. You will need all of the following code. Note the comments in the code. I have now included code to allow the user to skip the textbox and leave blank without invoking the validation. You will get problems even on closing the form if the user for any reason needs to leave the field blank. 'Following Dim statement must be in Declarations _ area before any Subs or code. Dim bolValid As Boolean Private Sub TextBox2_AfterUpdate() Dim dateValid As Date 'The following If/End If aborts validation _ if TextBox2 is empty. (Delete if not required) If Me.TextBox2 = "" Then Exit Sub End If On Error Resume Next dateValid = DateValue(Me.TextBox2) On Error GoTo 0 If dateValid 0 Then bolValid = True Else bolValid = False MsgBox "Invalid date format in TextBox2" _ & vbCrLf & "Must be dd/mm/yy format" Exit Sub End If If Year(dateValid) = Year(Date) Then bolValid = True Else bolValid = False MsgBox "Invalid Year in TextBox2" & _ vbCrLf & "Must be " & Year(Date) Exit Sub End If If Month(dateValid) = Month(Date) Then bolValid = True Else bolValid = False MsgBox "Invalid Month in TextBox2" & _ vbCrLf & "Must be " & Month(Date) Exit Sub End If If dateValid = DateValue(Me.TextBox1) Then bolValid = True Else bolValid = False MsgBox "Invalid date in TextBox2" & _ vbCrLf & "Must be = to TextBox1" Exit Sub End If End Sub Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean) 'Following line allows user to skip if leaving blank 'Remove [And Me.TextBox2 < ""] if you don't want this If bolValid = False And Me.TextBox2 < "" Then Cancel = True Me.TextBox2.SetFocus End If End Sub -- Regards, OssieMac |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox validation code
You have an active thread elsewhere.
"tkraju via OfficeKB.com" wrote: In my userform I have 2 TextBoxes. I nedd code for validation of TextBox2 is entry : it should be Date,should be current month and current year date,entry date should be greater than or equal to textbox1. Thank You. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200905/1 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Textbox validation | Excel Programming | |||
textbox validation | Excel Programming | |||
textbox value validation | Excel Programming | |||
Textbox validation | Excel Programming | |||
textbox validation | Excel Programming |