Validating set of entries

[lucpian]

Hi,

Thanks, a lot xld for the help with the validation code for date format. Like I said, I am very new to vba. I am working on validating the length of entries in column E of my worksheet which I am required to use vba code for. The length should not be less than or more than 4. Please, can anyone help me with sample code.

Thanks

Lucpian

[Bob Phillips]

Dim LastRow As Long
Dim cell As Range
Dim col As Range
Dim msg As String
Dim i As Long

For Each col In Range("A:C").Columns

LastRow = Cells(Rows.Count, col.Column).End(xlUp).Row
For Each cell In Cells(1, col.Column).Resize(LastRow)

If Len(cell.Value) <> 4 Then

msg = msg & cell.Address(False, False) & " - " & cell.Value & vbNewLine
End If
Next cell
Next col

MsgBox msg

[gwkenny]

I just can't help but think this is homework.

"which I am required to use vba code for"

Cause the validation can easily have been done at the time of data entry with Excel's inherent Data Validation.

[lucpian]

Hi,

Thanks,again xld, for the help with the validation code for validating the length of entries in column E of my worksheet which I am required to use vba code for. I am, however, still having problem because your code is not narrowed to just column E. Please, I still need your help.

Thanks

Lucpian

[Bob Phillips]

Dim LastRow As Long
Dim cell As Range
Dim col As Range
Dim msg As String
Dim i As Long

For Each col In Range("E:E").Columns

LastRow = Cells(Rows.Count, col.Column).End(xlUp).Row
For Each cell In Cells(1, col.Column).Resize(LastRow)

If Len(cell.Value) <> 4 Then

msg = msg & cell.Address(False, False) & " - " & cell.Value & vbNewLine
End If
Next cell
Next col

MsgBox msg

[lucpian]

Thanks, again xld. Is there any vba code that can also set the column E mandatory? I need your help.

Thanks

Lucky

[Bob Phillips]

I don't know what that means.

[lucpian]

Basically, there are about three columns with heading such as First name, Last name and SSN that need to be checked to ensure that the excel sheet has. These are required columns. So, I was hoping you may have a code that can do that. I have the following code, but it is not working.

Private Sub WorksheetMandatory_Change(ByVal Target As Range)
If Target.Column <> 3 Then Exit Sub
If Target.Offset(0, -1) = "" Then
Target.Select
Application.EnableEvents = False
Target = ""
Application.EnableEvents = True
MsgBox ("You must complete column B first")
End If

End Sub

What am I doing wrong?

Thanks

lucpian