vba textbox data valdiation

[CuriousGeorg]

I have a code for a text box to ensure that the date is entered correctly

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)'check date format
With TextBox1
If Len(.Text) <> 10 Or _
Mid(.Text, 3, 1) <> "-" Or _
Mid(.Text, 6, 1) <> "-" Then
MsgBox "Date format must be mm-dd-yyyy"
Cancel = True
Exit Sub
End If
End With
If Not IsDate(TextBox1) Or Mid(TextBox1, 1, 2) > 12 Then
MsgBox "Please enter a valid date."
Cancel = True
Exit Sub
End If
End Sub

This shows ensures the date is mm-dd-yyyy. Because I dont understand that code in the slightest.. what do i change to ensure the date is written dd-mm-yyyy

[SamT]

Hey George,

Len(gth) of text = Character count of "mm-dd-yyyy" or "dd-mm-yyyy" = 10

Mid(.Text, 3, 1) of the old "mm-dd-yyyy" is start at 3 character and return 1 character = "-"
You want to return 1 character from "dd-mm-yyyy" starting at position 3 = Mid(.Text, 3, 1)

Mid(.Text, 6, 1) o the old "mm-dd-yyyy" is start at 6 character and return 1 character = "-"
You want to return 1 character from "dd-mm-yyyy" starting at position 6 = Mid(.Text, 6, 1)

Then you want to tell them that the format must be ""Date format must be dd-mm-yyyy "

In the next IF: Mid(TextBox1, 1, 2) > 12 checks the first 2 characters to see if the month is greater than 12
YOu need Mid(TextBox1, 4, 2) > 12, because your new month starts at the 4th character


So the only thing you really have to change is the MsgBox string, and the check month test.

Check out this sub; The IsDate checks to see if the .Text value is any valid date format and that all the numerical values are correct; ie, it wont accept "31-13-2013" so program flow would pass to the Else Statement. CDate converts the string from .Text into a date for the Format function.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'check date format
    With TextBox1
        If IsDate(.Text) Then 'Format as desired.
          .Text = Format(CDate(.Text), "dd-mm-yyyy")
        Else
          .Text = "" 'Clear the TextBox
          MsgBox "Please enter a valid date."
          Cancel = True
          Exit Sub
        End If

'Test for date in correct range
        If CDate(.Text) > Now + 30 Then 'Sooner that 30 days in future, adjust as needed
         .Text = ""
          MsgBox "Please enter a valid date."
          Cancel = True
        End If
   End With
End Sub

[CuriousGeorg]

I feel such an idiot!!


Thanks again Sam

(your resident nubstick George)

[CuriousGeorg]

so on the back of that also..

Private Sub cmdCancel_Click() Unload Me
End Sub

Private Sub cmdClear_Click()
' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
 ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
 ctl.Value = False
End If
Next ctl
End Sub

Private Sub cmdOK_Click()
Dim RowCount As Long
Dim ctl As Control
' Check user input
If Me.cbohandler.Value = "" Then
 MsgBox "Please enter YOUR Name.", vbExclamation, "Handler missing"
 Me.cbohandler.SetFocus
Exit Sub
End If
If Me.cbohandler.Value = "" Then
 MsgBox "Please enter your TEAM's Name.", vbExclamation, "Handler missing"
 Me.cbohandler.SetFocus
Exit Sub
 End If
 If Me.txtDOL.Value = "" Then
 MsgBox "Please enter a Date of Loss.", vbExclamation, "Date of Loss Missing"
 Me.txtDOL.SetFocus
Exit Sub
 End If
If Me.txtDOR.Value = "" Then
 MsgBox "Please enter date of review.", vbExclamation, "Date of Review missing"
 Me.txtDOR.SetFocus
Exit Sub
 End If
If Me.cbocontact.Value = "" Then
 MsgBox "Please enter who contact was with.", vbExclamation, "Contact Missing"
 Me.cbocontact.SetFocus
Exit Sub
 End If
If Me.cbomethod.Value = "" Then
 MsgBox "Please enter method of contact.", vbExclamation, "Contact Method Missing"
 Me.cbomethod.SetFocus
Exit Sub
 End If


' Write data to worksheet
 RowCount = Worksheets("comms").Range("B7").CurrentRegion.Rows.Count
With Worksheets("comms").Range("B7")
 
 .Offset(RowCount, 0).Value = Me.cbohandler.Value
 .Offset(RowCount, 1).Value = Me.cboteamname.Value
 .Offset(RowCount, 2).Value = Format(CDate(Me.txtweekc.Value), "dd/mm/yyyy")
 .Offset(RowCount, 3).Value = Me.txtClaim.Value
 .Offset(RowCount, 4).Value = Format(CDate(Me.txtDOL.Value), "dd/mm/yyyy")
 .Offset(RowCount, 5).Value = Format(CDate(Me.txtDOR.Value), "dd/mm/yyyy")
 .Offset(RowCount, 6).Value = Me.cbocontact.Value
 .Offset(RowCount, 7).Value = Me.cbomethod.Value
 .Offset(RowCount, 8).Value = txtreason.Value


 End With
' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
 ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
 ctl.Value = False
End If
 Next ctl
  ThisWorkbook.Save
End Sub

Private Sub txtweekc_Exit(ByVal Cancel As MSForms.ReturnBoolean)
     'check date format
    With txtweekc
        If IsDate(.Text) Then 'Format as desired.
            .Text = Format(CDate(.Text), "dd/mm/yyyy")
        Else
            .Text = "" 'Clear the TextBox
            MsgBox "Please enter a valid date."
            Cancel = True
            Exit Sub
        End If
         
         'Test for date in correct range
        If CDate(.Text) > Now + 30 Then 'Sooner that 30 days in future, adjust as needed
            .Text = ""
            MsgBox "Please enter a valid date."
            Cancel = True
        End If
    End With
End Sub

Is there ANY reason in the above code why the dates are pulling back onto the worksheet as USA dates? (i know i'm missing 2 date fields with the validation but i test everytime i add a piece of code and for some reason this is pulling through US dates)

[SamT]

Go to the Windows Control Panel and open Regional And Language Options.

Also try adding an Asterisk to the front of the Format string: "*dd-mm-yyyy".

Finally make sure the Cell(s) themselves are formatted to UK style Dates.

For more info search Excel and Windows Help files for "International Dates"

BTW, You have this If test twice: "If Me.cbohandler.Value = "" Then"

IF you want to set the allowed dates to no earlier then X

If CDate(.Text) < Now - 30 OR CDate(.Text) > Now Then 'Between 30 days ago and today

When you get it all working, I'll give some suggestions to make it easier to understand and more User Friendly.

[SamT]

Her's an example of how one function can check the daters of all date TextBoxes.

Private Sub txtweekc_Exit(ByVal Cancel As MSForms.ReturnBoolean)
 'Sample Date TextBox Exit Event Sub
Dim CancelMe As Boolean
  With txtweekc
    .Text = CheckDate(.Text, CancelMe)
  End With
  Cancel = CancelMe
 End Sub

 
 Private Function CheckDate(ByVal DateString As String, ByRef CancelMe As Boolean) As String
 'Check date format of DateString, Set CancelMe in Calling Sub
 'Returns formatted date if input DateString is good, else returns Empty String
        If IsDate(DateString) Then 'Format as desired.
            CheckDate = CStr(Format(CDate(DateString), "dd/mm/yyyy"))
        Else
            CheckDate = ""
            MsgBox "Please enter a valid date."
            CancelMe = True
            Exit Function
        End If
         
         'Test for date in correct range
        If CDate(CheckDate) < Now - 30 Or CDate(CheckDate) > Now Then 'Between 30 days ago and today
            CheckDate = ""
            MsgBox "Please enter an allowed date."
            CancelMe = True
        End If
End Function

[CuriousGeorg]

Thanks for that Sam, a problem I will have with that is...

there are a couple of text boxes that will have numbers in that wont be dates so, if that formats EVERY text box into a date then it wont work.

However, in relation to my issue, it appears (for now) that it was the "format(CDATE" that was the problem. Just leaving ti at Cdate let it work, which is wierd as it didnt before.

I wish i could access "Go to the Windows Control Panel and open Regional And Language Options.". but its restricted on our work computers.

Thanks for the heads up on the cbo.handler thing.. I did notice it before but didnt change it at the time as i was looking for something else. Doesn't generate an error and it has been fixed since posting.


One thing ive started doing in my code is quoting what does what with the ' .... makes my life a lot easier when im coping parts of my own code then adapting to other areas.. (even if it makes most of my screen green!)

frmhulk.show

Thanks for your help. It's taken me a while to get where I feel comfortable just changing bits of code and seeing if it improves/weakens etc then when i break it knowing I have people who will assist

[snb]

Why not populating a combobox (style dropdownlist) with only valid dates ?
The user can enter a date or select a date; no need to check anything afterwards; no irritating messages as if the user did anything 'wrong'.
If the user can do anything 'wrong' it's the programmer to blame.

You can find a bunch of methods to populate comboboxes with only valid options here:

http://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html

[CuriousGeorg]

The problem I have with combo boxes is user satisfaction. If they are adding say three dates to a user form then it is (at least for my guys) more beneficial that they can have a user text box where if needed they can also copy and paste as well as type.

too many combo boxes isnt ideal

[snb]

I fear you didn't explore my suggestion well enough.
All the things you mention can be applied to comboboxes too.
The user will never get a messagebox (s)he made a mistake.
Too many textboxes neither.

[SamT]

George,

On the two subs that check "all" TextBoxes"... Read the name of the first one and see what it is doing.
For any Ctrl that needs it's date checked, all subs are identical to the one above. Only the names in it are changed.


The worst time for date errors is in the first two weeks of January when the year is usually off by -1. Making sure that no future dates are set for past actions can be a good thing too.

The best reason for checking the date's range is to insure that actions are take in due time. That's what we call a Business Rule.

I have found that you can make the UF more attractive and more satisfying to use by declaring two constants; cboDefaultHeight and cboSelectedHeight. Set the value of cboDefaultHeight to the same as the TextBoxes' height and cboSelectedHeight to a value that shows about 6 list items. Then use the ComboBoxes' Get and Lost Focus Events to set them to either of the Constants as indicated by name.

If you have ComboBoxes too close to the bottom of the form, you can use the Constants [ComboBoxName]DefaultTop and [ComboBoxName]SelectedTop to move the Ctrl up when it gets the focus. These constant's values must be specific to the Ctrl.