Multiple data validations on an excel table field

[jayexcel1]

Hi,
Please see attached template file.

I have the below mentioned fields in a table "MyData"

• ID - AutoNumber (generate automatically if an entry is made in Column B; 1 till 1000 in ascending order)

• First Name and Last Name - Both these fields are mandatory(cannot be empty/blank)

• Primary Contact Number - This field is mandatory with format "0000 00 0000". Length of the field is 12 digits and must be between 7777777777 and 9999999999. Duplicate entry is not allowed for this field.
• Secondary Contact Number - Similar to Primary contact number but not mandatory and duplicates are allowed.

• Email - this field must contain @ and .com

• The below fields are inter dependant data validation list boxes

Region -
City -
Venue -
Role -
Status -

All the above fields must be in Proper case and user is not allowed to copy and paste values in the columns A:F.

Request any help on this.

Regards,
jay.

 

[Chihiro]

If you want strict adherence on data entry, I'd recommend using UserForm (VBA) instead of data validation on the sheet and protecting the sheet from edit.

ID - Since it's just sequential numbering you can use Row#-1

First & Last - Simple if statement

Primary Contact - Check for Length and check if it's number. Link for how to check if textbox is numbers only.
http://www.ozgrid.com/VBA/validate-numbers.htm

For duplicate in range, you can stick something like below before you commit the data to sheet.

Dim cRange As Range
Dim lRow As Integer
Dim cDup As String

lRow = Sheets("shMyData").Range("D" & Rows.Count).End(xlUp).Row
Set cRange = Sheets("shMyData").Range("D2:D" & lRow)
cDup = UserForm1.TextBox1.Value

If Application.WorksheetFunction.CountIf(cRange, cDup) > 0 Then
    MsgBox "Duplicate Found"
    UserForm1.TextBox1.Clear
Else
End If

Email use code found in link.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=281

Dependent List - See links
http://www.excel-easy.com/vba/examples/dependent-combo-boxes.html
http://ccm.net/faq/36243-vba-how-to-create-a-cascading-combo-box-in-a-userform