Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If data in one column, take date, add 2 days, and turn the entire column a color... | Excel Discussion (Misc queries) | |||
my column is sorted in two sections. How do I sort entire column? | Excel Discussion (Misc queries) | |||
my column is sorted in two sections. How do I sort entire column? | Excel Discussion (Misc queries) | |||
Find Column heading and then Delete entire column | Excel Programming | |||
Code for deleting entire row | Excel Programming |