VBA not 100% need help with variable
 

I have this code that will only allow an "x" entered in a cell from
H9:J400.
The way it is now, If an input is entered in one of these cells other
than a "x" a message box pops up stating only an x can be entered here.
It will not currently allow me to delete and leave a cell blank(lets say
a mistake was made) and continue on with the sheet.

I need for this code to accept either an "x" or "" ANY SUGGESTIONS
PLEASE!

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TestCell As Range, Invalid As Boolean
If Not (Intersect(Target, Range("H9:J400")) Is Nothing) Then
For Each TestCell In Target.Cells
Invalid = Invalid Or TestCell.Value < "X"
Next TestCell
End If
If Invalid Then
MsgBox "You must enter 'X' here"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End Sub


--
parteegolfer
------------------------------------------------------------------------
parteegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31951
View this thread: http://www.excelforum.com/showthread...hreadid=521889

VBA not 100% need help with variable
 

Invalid = Invalid Or (TestCell.Value < "X" And TestCell.Value < "")

"parteegolfer" wrote:


I have this code that will only allow an "x" entered in a cell from
H9:J400.
The way it is now, If an input is entered in one of these cells other
than a "x" a message box pops up stating only an x can be entered here.
It will not currently allow me to delete and leave a cell blank(lets say
a mistake was made) and continue on with the sheet.

I need for this code to accept either an "x" or "" ANY SUGGESTIONS
PLEASE!

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TestCell As Range, Invalid As Boolean
If Not (Intersect(Target, Range("H9:J400")) Is Nothing) Then
For Each TestCell In Target.Cells
Invalid = Invalid Or TestCell.Value < "X"
Next TestCell
End If
If Invalid Then
MsgBox "You must enter 'X' here"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End Sub


--
parteegolfer
------------------------------------------------------------------------
parteegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31951
View this thread: http://www.excelforum.com/showthread...hreadid=521889

VBA not 100% need help with variable
 

How about:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim TestCell As Range
Dim Invalid As Boolean
Dim myIntersect As Range

Set myIntersect = Intersect(Target, Me.Range("h9:j400"))

If myIntersect Is Nothing Then Exit Sub

On Error Resume Next
For Each TestCell In myIntersect.Cells
If LCase(TestCell.Text) = "x" _
Or LCase(TestCell.Text) = "" Then
'ok
Else
MsgBox "You must enter 'X' here"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit For 'stop looking for more errors
End If
Next TestCell
On Error GoTo 0

End Sub




parteegolfer wrote:

I have this code that will only allow an "x" entered in a cell from
H9:J400.
The way it is now, If an input is entered in one of these cells other
than a "x" a message box pops up stating only an x can be entered here.
It will not currently allow me to delete and leave a cell blank(lets say
a mistake was made) and continue on with the sheet.

I need for this code to accept either an "x" or "" ANY SUGGESTIONS
PLEASE!

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TestCell As Range, Invalid As Boolean
If Not (Intersect(Target, Range("H9:J400")) Is Nothing) Then
For Each TestCell In Target.Cells
Invalid = Invalid Or TestCell.Value < "X"
Next TestCell
End If
If Invalid Then
MsgBox "You must enter 'X' here"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End Sub

--
parteegolfer
------------------------------------------------------------------------
parteegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31951
View this thread: http://www.excelforum.com/showthread...hreadid=521889

--

Dave Peterson