PDA

View Full Version : Validating set of entries



lucpian
01-30-2008, 09:55 AM
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
01-30-2008, 10:28 AM
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
01-30-2008, 04:47 PM
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
01-31-2008, 12:07 PM
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
01-31-2008, 12:09 PM
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
01-31-2008, 02:20 PM
Thanks, again xld. Is there any vba code that can also set the column E mandatory? I need your help.

Thanks

Lucky

Bob Phillips
01-31-2008, 04:34 PM
I don't know what that means.

lucpian
02-01-2008, 11:22 AM
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