VBA not 100% need help with variable

[parteegolfer]

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

[Toppers]

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
>
>

[Dave Peterson]

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