PDA

View Full Version : [SOLVED] vba textbox data valdiation



CuriousGeorg
09-12-2013, 02:01 AM
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
09-12-2013, 07:16 AM
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
09-12-2013, 07:35 AM
I feel such an idiot!!


Thanks again Sam

(your resident nubstick George)

CuriousGeorg
09-12-2013, 08:42 AM
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
09-12-2013, 10:38 AM
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". :dunno

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
09-12-2013, 11:19 AM
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
09-13-2013, 12:08 AM
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
09-13-2013, 01:57 AM
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
09-13-2013, 03:23 AM
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
09-13-2013, 06:46 AM
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
09-13-2013, 06:49 AM
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.