Validating data pasted into worksheet

[Guest]

I have an application that has a user paste data into a set group of columns
and up to 100 rows. The data is sales information. They then click a button
and the data is exported into a flat file. I need the data to be validated
before the flat file is created. 1. I need field length validation on some
columns (13 characters max) and 2. I need the data in two columns to be
validated against a code list (MN, KN, TD, or GD). I am thinking that doing
this as a Macro is best because selecting the "Data" "Validation" for a
range is erased once new data is pasted into a cell.

 

[Dave Peterson]

This seems like a very reasonable approach.

I have an application that has a user paste data into a set group of columns
and up to 100 rows. The data is sales information. They then click a button
and the data is exported into a flat file. I need the data to be validated
before the flat file is created. 1. I need field length validation on some
columns (13 characters max) and 2. I need the data in two columns to be
validated against a code list (MN, KN, TD, or GD). I am thinking that doing
this as a Macro is best because selecting the "Data" "Validation" for a
range is erased once new data is pasted into a cell.

 

[Guest]

Do you have any sample code?

This seems like a very reasonable approach.

 

[Dave Peterson]

How about something like this:

Option Explicit
Sub testme()

Dim myRng As Range
Dim okToContinue As Boolean

With ActiveSheet
Set myRng = .Range("a2:g" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

okToContinue = ValidateMyData(myRng)

If okToContinue = False Then
MsgBox "You had at least one error!"
Exit Sub
End If

'do your export here.

End Sub

Function ValidateMyData(myRng As Range) As Boolean

Dim myCol As Range

ValidateMyData = True
For Each myCol In myRng.Columns
Select Case myCol.Column
Case Is = 1
If DoLength(myCol, 13) = False Then
ValidateMyData = False
End If
Case Is = 2
If DoLength(myCol, 4) = False Then
ValidateMyData = False
End If
Case Is = 5
If DoList(myCol, Array("md", "xy", "zz")) = False Then
ValidateMyData = False
End If
End Select
Next myCol

End Function
Function DoLength(myRng As Range, MaxLen As Long) As Boolean
DoLength = CBool(Application.Evaluate("Max(len(" & _
myRng.Address(external:=True) & "))") <= MaxLen)
If DoLength = False Then
MsgBox "Something too wide with: " & myRng.Address(0, 0) & vbLf _
& "no more than: " & MaxLen & " characters!"
End If
End Function
Function DoList(myRng As Range, myList As Variant) As Boolean
Dim myCell As Range
Dim res As Variant
DoList = True
For Each myCell In myRng.Cells
res = Application.Match(myCell.Value, myList, 0)
If IsError(res) Then
MsgBox "Invalid entry in: " & myRng.Address(0, 0)
DoList = False
Exit For
End If
Next myCell

End Function