Code for an entire column
 

I would like this code to validate the data for all of column F - any
suggestions?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo OuttaHere
If Target.Address = "$F$4" Then '<<<< change cell
Application.EnableEvents = False
Dim strText As String
Dim lngN As Long
Const str_Chars As String = "[0-9a-zA-Z ]"
strText = Target.Text

For lngN = 1 To Len(strText)
If Not Mid$(strText, lngN, 1) Like str_Chars Then
MsgBox "Only numbers or alphabetic characters allowed. ", _
vbOKOnly, "Data Validation"
Application.Undo
Exit For
End If
Next 'lngN
End If
OuttaHe
Application.EnableEvents = True
End Sub

Code for an entire column
 

Try this:
If Not Intersect(Target, Range("F:F")) Is Nothing Then
Instead of:
If Target.Address = "$F$4" Then

--
Charles Chickering

"A good example is twice the value of good advice."


"Kimberly" wrote:

I would like this code to validate the data for all of column F - any
suggestions?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo OuttaHere
If Target.Address = "$F$4" Then '<<<< change cell
Application.EnableEvents = False
Dim strText As String
Dim lngN As Long
Const str_Chars As String = "[0-9a-zA-Z ]"
strText = Target.Text

For lngN = 1 To Len(strText)
If Not Mid$(strText, lngN, 1) Like str_Chars Then
MsgBox "Only numbers or alphabetic characters allowed. ", _
vbOKOnly, "Data Validation"
Application.Undo
Exit For
End If
Next 'lngN
End If
OuttaHe
Application.EnableEvents = True
End Sub

Code for an entire column
 

Thank you very much!

"Charles Chickering" wrote:

Try this:
If Not Intersect(Target, Range("F:F")) Is Nothing Then
Instead of:
If Target.Address = "$F$4" Then

--
Charles Chickering

"A good example is twice the value of good advice."


"Kimberly" wrote:

I would like this code to validate the data for all of column F - any
suggestions?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo OuttaHere
If Target.Address = "$F$4" Then '<<<< change cell
Application.EnableEvents = False
Dim strText As String
Dim lngN As Long
Const str_Chars As String = "[0-9a-zA-Z ]"
strText = Target.Text

For lngN = 1 To Len(strText)
If Not Mid$(strText, lngN, 1) Like str_Chars Then
MsgBox "Only numbers or alphabetic characters allowed. ", _
vbOKOnly, "Data Validation"
Application.Undo
Exit For
End If
Next 'lngN
End If
OuttaHe
Application.EnableEvents = True
End Sub

Code for an entire column
 

Change

If Target.Address = "$F$4" Then

to this.............

If Not Application.Intersect(Target, Columns("F:F")) Is Nothing Then


Gord Dibben MS Excel MVP

On Wed, 7 Feb 2007 13:00:01 -0800, Kimberly
wrote:

I would like this code to validate the data for all of column F - any
suggestions?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo OuttaHere
If Target.Address = "$F$4" Then '<<<< change cell
Application.EnableEvents = False
Dim strText As String
Dim lngN As Long
Const str_Chars As String = "[0-9a-zA-Z ]"
strText = Target.Text

For lngN = 1 To Len(strText)
If Not Mid$(strText, lngN, 1) Like str_Chars Then
MsgBox "Only numbers or alphabetic characters allowed. ", _
vbOKOnly, "Data Validation"
Application.Undo
Exit For
End If
Next 'lngN
End If
OuttaHe
Application.EnableEvents = True
End Sub