UserForm Data Validation

[reberryjr]

I have a UserForm that has 43 text and combo boxes. I want to introduce some code that verifies that certain fields are completed, and in certain formats. An example is "txt_Updated". This field is required to be completed. I added the code below, but want the User to be able to either enter MM/DD/YY, MM/D/YY, M/D/YY, M/DD/YY, depending upon the actual date. In essence, I'm really just trying to ensure that the text box isn't null, text or some weird entry like 123/1/17, when it should be 12/31/17.

The other thing that I'd like to do is have all of this validation take place prior to the code starting to enter data into the various worksheets, so that I don't wind up with partial data entry.

Do I have to add all of this validation before the code starts, or is there a way to validate as the code runs, and if a rule is not met, have the data entry not occur? I'm trying to keep the code as clean as possible and it feels as though it's starting to get a bit messy.

I've only included some of the code below, as it's pretty long and I'm hoping to extrapolate the response I receive here, to the rest of the code. Specific questions/examples are in red font.

Private Sub cmd_Submit_Click()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet


Dim NextRow As Long
Dim NextRow2 As Long
Dim NextRow3 As Long


Set ws1 = ThisWorkbook.Sheets("Bios")
Set ws2 = ThisWorkbook.Sheets("Stats")
Set ws3 = ThisWorkbook.Sheets("Financials")
Set ws4 = ThisWorkbook.Sheets("Variables")


NextRow = ws1.Range("D" & Rows.Count).End(xlUp).Row + 1
NextRow2 = ws2.Range("C" & Rows.Count).End(xlUp).Row + 1
NextRow3 = ws3.Range("D" & Rows.Count).End(xlUp).Row + 1


[COLOR=#ff0000]'Can I add this type of code to the executing line, so that I can add a more descriptive message box, or do I HAVE to do the validation before the code starts to run?[/COLOR]
If Me.txt_Height.Value = "" Or Me.txt_Weight.Value = "" Or Me.txt_Chest.Value = "" Or Me.txt_Waist.Value = "" Or Me.txt_Hips.Value = "" Then
    MsgBox "Please verify that you have entered a value for Height, Weight, Chest, Waist and Hips."
Else


ws1.Range("A" & NextRow).Value = "=Today()"
[COLOR=#ff0000]'Here is the code validating the Updated date, mentioned in the post.[/COLOR]
If Not Me.txt_Updated.Value Like "##[/]##[/]##" Then
    MsgBox "Please enter a valid date."
Else
ws1.Range("B" & NextRow).Value = CDate(Me.txt_Updated) 'This should be the date that the Client provided the updated figures.
End If
ws1.Range("C" & NextRow).Value = "Active"
ws1.Range("D" & NextRow).Value = CInt(Me.txt_Key)
With ws1.Range("E" & NextRow)
    .Hyperlinks.Add _
    anchor:=.Offset(), _
    Address:="", _
    SubAddress:="'" & Me.txt_ClientID.Value & "'!AW1", _
    TextToDisplay:=Me.txt_ClientID.Value
End With
ws1.Range("F" & NextRow).Value = Me.txt_First
ws1.Range("G" & NextRow).Value = Me.txt_Last
ws1.Range("H" & NextRow).Value = Me.txt_Suff
ws1.Range("I" & NextRow).Value = Me.txt_Name
ws1.Range("J" & NextRow).Value = Me.cobo_Gender
ws1.Range("K" & NextRow).Value = CDate(Me.txt_DoB)
ws1.Range("L" & NextRow).Value = CInt(Me.txt_SignupAge)
ws1.Range("M" & NextRow).Value = "=IF(RC[-2]="""","""",INT(RC[-12]-RC[-2])/365.25)"
ws1.Range("N" & NextRow).Value = (Me.txt_Phone)
With ws1.Range("O" & NextRow)
    .Hyperlinks.Add _
    anchor:=.Offset(), _
    Address:="mailto:" & Me.txt_Email.Value, _
    TextToDisplay:=Me.txt_Email.Value
End With


ws2.Range("A" & NextRow2).Value = "=Today()"
ws2.Range("B" & NextRow2).Value = CDate(Me.txt_Updated)
ws2.Range("C" & NextRow2).Value = Me.txt_ClientID
ws2.Range("D" & NextRow2).Value = Me.txt_Name
ws2.Range("E" & NextRow2).Value = "Initial"
ws2.Range("F" & NextRow2).Value = Me.txt_Height
[COLOR=#FF0000]'Can I add validation in this line that ensures the value entered is numeric and if it isn't, prevents any of the code from executing?[/COLOR]
ws2.Range("G" & NextRow2).Value = CStr(Me.txt_Weight)

I've been working on this for a few hours and am unsure about the best way to structure the code.

 

[reberryjr]

@dmt32, I made a test file and pasted your code in it. It works great! Now, in trying to understand it, I do have some questions.
1. I'm not understanding how the LBound and UBound come into play.
2. How can the Control Type (2) check both Date and Number?
3. How do we bypass declaring what the 3 different Control Types are? UserForm, TextBox, etc.
4. What does the 48 represent in this line?

If Not IsValidData Then .SetFocus: MsgBox ControlName(1), 48, "Entry Required": Exit Function

Sorry for all of the questions, but I am new to a lot of this language and want to understand it so I can use it in the future.

 

[dmt32]

deleted

 

[dmt32]

@dmt32, I made a test file and pasted your code in it. It works great! Now, in trying to understand it, I do have some questions.
1. I'm not understanding how the LBound and UBound come into play.
2. How can the Control Type (2) check both Date and Number?
3. How do we bypass declaring what the 3 different Control Types are? UserForm, TextBox, etc.
4. What does the 48 represent in this line?

If Not IsValidData Then .SetFocus: MsgBox ControlName(1), 48, "Entry Required": Exit Function

Sorry for all of the questions, but I am new to a lot of this language and want to understand it so I can use it in the future.

1 – LBound is the first element of the array – UBound is the last element of array.

You can read more about arrays here:https://excelmacromastery.com/excel-vba-array/

2 – I think you are referring to the string array ControlName(2)? If so, this is the 3^rd element in the array (lower bound of this array is 0) which returns one of the following as a string:

xlDate, xlNumber, xlText.

These are the names of excel Enumerated constants that I use in the code as it makes it easier to understand using a descriptive name rather than just a numeric value. xlDate for instance has a numeric value of 2.

I change the string names back to their numeric values in this line of code:

DataType = IIf(ControlName(2) = "xlDate", xlDate, IIf(ControlName(2) = "xlNumber", xlNumber, xlText))

DataType variable is declared as a Varaint which accommodates different data types this allows the change from string to numeric value.
The purpose of this part of code is to simply determine what data type you have restricted the textbox to – this is then used is the Select Case statement to go the correct case statement & complete all required tests.

Dates are numbers.

3 – sorry don’t understand your question?

4 – 48 is the value of enumerated constant vbExclamation. This value provides the OK button & exclamation symbol in the msgbox.
Whilst in most cases I use contstants in my code as they make it clearer to read, I nearly always just use the numeric values in msgbox prompts – it’s just a personal thing.

Dave

 

[reberryjr]

Thank you for the explanation! I found some stuff on LBound and UBound, but it wasn't very clear. Your explanation is much simpler and clearer. My 3rd question was about declarations. For example, if I have to Dim a Range, why wouldn't I have to Dim the control types? Or, is it just something that VBA "knows"?

I appreciate all of the help as I try to learn this stuff.

Cheers!

 

[dmt32]

Thank you for the explanation! I found some stuff on LBound and UBound, but it wasn't very clear. Your explanation is much simpler and clearer. My 3rd question was about declarations. For example, if I have to Dim a Range, why wouldn't I have to Dim the control types? Or, is it just something that VBA "knows"?

I appreciate all of the help as I try to learn this stuff.

Cheers!

have a look here:http://www.globaliconnect.com/excel...e-data-types-in-excel-vba&catid=79&Itemid=475

which may help there are other sites you can visit.

I noticed after I posted that it was not necessary for the conversion of the enumerated constants from string I passed in array back to their numeric values - I took most of the code from another application I did that reads from a table for the controls & their attributes which are all text hence need for conversion - as I created a variant array of the controls for your application I could have just passed their numeric values from there. Its no big deal though.

Glad suggestion has helped

Dave

 

[reberryjr]

@dmt32, it's been awhile since we touched on this topic, but after making some structural changes and getting the final requirements outlined, I'm back to this one. I really, REALLY like being able to add these controls at once, instead of writing several if statements. A couple of questions about the flexibility of the code you offered in post 9.

1. Is it possible to add an if statement to this, that looks to see if the LEN of the control is 0 or not? Reason behind the question is, some of these fields cannot be blank, so the format check should capture them if they are. There are several that are ok to be blank, but if they aren't, that's when the format check should kick in.

2. Many of my forms have the same need for format checking, with many of the same fields. Since I don't see any declaration of the actual form name, I'm assuming that I don't have to duplicate this code for each form. However, will it run awry if some of the controls being checked, aren't on all of the forms? Example: txt_Weight is on frm_AddNew and frm_UpdateStats, but it is not on frm_UpdateBios (and a couple of other ones). If I call the module from the frm_UdpateBios, will I get errors for the fields that aren't on the form? I'm asking b/c I haven't tried it yet, b/c if I can't solve for the 1st question, I may not be able to leverage this code in this application. I'm also asking this question prior to testing, b/c I wanted to get all of my known questions out there in one post, versus multiple ones.

Hopefully all of that makes sense, and didn't come across as "word vomit".

 

[dmt32]

1. Is it possible to add an if statement to this, that looks to see if the LEN of the control is 0 or not? Reason behind the question is, some of these fields cannot be blank, so the format check should capture them if they are. There are several that are ok to be blank, but if they aren't, that's when the format check should kick in.

The IsValidData Function does not allow blank entries.

Function RequiredControls is an array of ALL controls that require data entry.
Each control in the array is checked by IsValidData Function for correct data type entry - blank values fail for Numeric & Date entries. For Text values these are tested as follows

IsValidData = CBool(Len(.Text) > 0)

So blank entry will fail

2. Many of my forms have the same need for format checking, with many of the same fields. Since I don't see any declaration of the actual form name, I'm assuming that I don't have to duplicate this code for each form.

You pass an instance of the Userform to IsValidData Function argument Form using Me keyword. So no you don’t need to duplicate it.

However, will it run awry if some of the controls being checked, aren't on all of the forms?

If you are going to use Function with other userforms then will need to create their own Required Controls array listing controls name, message prompts & Data Types.
Some modification to IsValidData function will be required to accommodate different required control arrays.

Dave

 

[reberryjr]

Ok, because I need to allow some fields to be blank, I can't leverage this code for everything, but I think it will cut down on a lot of coding.

Thank you VERY much for your assistance, and thorough explanations! They're very much appreciated!