"delete" key activates Sub Worksheet_Change

D

Dave

I have a form that does not allow users to input a "#"
symbol in three cells. The code below accomplishes this.
Trouble is using the "delete" key in any cell anywhere in
my form pulls up the MsgBox from my "Private Sub
Worksheet" below.

Any help would be appreciated.



Private Sub Worksheet_Change(ByVal Target As Excel.Range)

On Error Resume Next

Application.EnableEvents = False

If Union(Target, Me.Range("AH3,I11,T11")).Address =
Me.Range("AH3,I11,T11").Address _
And InStr(1, Target, "#") > 0 Then
Target.Select
MsgBox "The following symbols are not allowed in this
field! # "
Target = ""

End If
 
T

Tom Ogilvy

I pasted your code into a sheet module and couldn't reproduce the problem.

I did add a line to turn events back on at the end.
 
G

Guest

Thanks Tom, I only posted part of the code and after
further testing I realize that the entire code is creating
the trouble. Still don't know what it is but here is the
entire code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

On Error Resume Next

Application.EnableEvents = False

If Union(Target, Me.Range("AH3,I11,T11")).Address =
Me.Range("AH3,I11,T11").Address _
And InStr(1, Target, "#") > 0 Then
Target.Select
MsgBox "The following symbols are not allowed in this
field! # < > ? [ ] : * \ and / "
Target = ""

End If

If Union(Target, Me.Range("AH3,I11,T11")).Address =
Me.Range("AH3,I11,T11").Address _
And InStr(1, Target, "<") > 0 Then
Target.Select
MsgBox "The following symbols are not allowed in this
field! ""# < > ? [ ] : * \ and /"" "
Target = ""
End If

If Union(Target, Me.Range("AH3,I11,T11")).Address =
Me.Range("AH3,I11,T11").Address _
And InStr(1, Target, ">") > 0 Then
Target.Select
MsgBox "The following symbols are not allowed in this
field! ""# < > ? [ ] : * \ and /"" "
Target = ""
End If

If Union(Target, Me.Range("AH3,I11,T11")).Address =
Me.Range("AH3,I11,T11").Address _
And InStr(1, Target, "?") > 0 Then
Target.Select
MsgBox "The following symbols are not allowed in this
field! ""# < > ? [ ] : * \ and /"" "
Target = ""
End If

If Union(Target, Me.Range("AH3,I11,T11")).Address =
Me.Range("AH3,I11,T11").Address _
And InStr(1, Target, "[") > 0 Then
Target.Select
MsgBox "The following symbols are not allowed in this
field! ""# < > ? [ ] : * \ and /"" "
Target = ""
End If


If Union(Target, Me.Range("AH3,I11,T11")).Address =
Me.Range("AH3,I11,T11").Address _
And InStr(1, Target, "]") > 0 Then
Target.Select
MsgBox "The following symbols are not allowed in this
field! ""# < > ? [ ] : * \ and /"" "
Target = ""
End If

If Union(Target, Me.Range("AH3,I11,T11")).Address =
Me.Range("AH3,I11,T11").Address _
And InStr(1, Target, ":") > 0 Then
Target.Select
MsgBox "The following symbols are not allowed in this
field! ""# < > ? [ ] : * \ and /"" "
Target = ""
End If

If Union(Target, Me.Range("AH3,I11,T11")).Address =
Me.Range("AH3,I11,T11").Address _
And InStr(1, Target, "*") > 0 Then
Target.Select
MsgBox "The following symbols are not allowed in this
field! ""# < > ? [ ] : * \ and /"" "
Target = ""
End If

If Union(Target, Me.Range("AH3,I11,T11")).Address =
Me.Range("AH3,I11,T11").Address _
And InStr(1, Target, "\") > 0 Then
Target.Select
MsgBox "The following symbols are not allowed in this
field! ""# < > ? [ ] : * \ and /"" "
Target = ""
End If

If Union(Target, Me.Range("AH3,I11,T11")).Address =
Me.Range("AH3,I11,T11").Address _
And InStr(1, Target, "/") > 0 Then
Target.Select
MsgBox "The following symbols are not allowed in this
field! ""# < > ? [ ] : * \ and /"" "
Target = ""
End If


Application.EnableEvents = True
End Sub
 
T

Tom Ogilvy

There is nothing in your complete code that would cause that and it works
fine for me. I can't reproduce the behavior.

--
Regards,
Tom Ogilvy

Thanks Tom, I only posted part of the code and after
further testing I realize that the entire code is creating
the trouble. Still don't know what it is but here is the
entire code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

On Error Resume Next

Application.EnableEvents = False

If Union(Target, Me.Range("AH3,I11,T11")).Address =
Me.Range("AH3,I11,T11").Address _
And InStr(1, Target, "#") > 0 Then
Target.Select
MsgBox "The following symbols are not allowed in this
field! # < > ? [ ] : * \ and / "
Target = ""

End If

If Union(Target, Me.Range("AH3,I11,T11")).Address =
Me.Range("AH3,I11,T11").Address _
And InStr(1, Target, "<") > 0 Then
Target.Select
MsgBox "The following symbols are not allowed in this
field! ""# < > ? [ ] : * \ and /"" "
Target = ""
End If

If Union(Target, Me.Range("AH3,I11,T11")).Address =
Me.Range("AH3,I11,T11").Address _
And InStr(1, Target, ">") > 0 Then
Target.Select
MsgBox "The following symbols are not allowed in this
field! ""# < > ? [ ] : * \ and /"" "
Target = ""
End If

If Union(Target, Me.Range("AH3,I11,T11")).Address =
Me.Range("AH3,I11,T11").Address _
And InStr(1, Target, "?") > 0 Then
Target.Select
MsgBox "The following symbols are not allowed in this
field! ""# < > ? [ ] : * \ and /"" "
Target = ""
End If

If Union(Target, Me.Range("AH3,I11,T11")).Address =
Me.Range("AH3,I11,T11").Address _
And InStr(1, Target, "[") > 0 Then
Target.Select
MsgBox "The following symbols are not allowed in this
field! ""# < > ? [ ] : * \ and /"" "
Target = ""
End If


If Union(Target, Me.Range("AH3,I11,T11")).Address =
Me.Range("AH3,I11,T11").Address _
And InStr(1, Target, "]") > 0 Then
Target.Select
MsgBox "The following symbols are not allowed in this
field! ""# < > ? [ ] : * \ and /"" "
Target = ""
End If

If Union(Target, Me.Range("AH3,I11,T11")).Address =
Me.Range("AH3,I11,T11").Address _
And InStr(1, Target, ":") > 0 Then
Target.Select
MsgBox "The following symbols are not allowed in this
field! ""# < > ? [ ] : * \ and /"" "
Target = ""
End If

If Union(Target, Me.Range("AH3,I11,T11")).Address =
Me.Range("AH3,I11,T11").Address _
And InStr(1, Target, "*") > 0 Then
Target.Select
MsgBox "The following symbols are not allowed in this
field! ""# < > ? [ ] : * \ and /"" "
Target = ""
End If

If Union(Target, Me.Range("AH3,I11,T11")).Address =
Me.Range("AH3,I11,T11").Address _
And InStr(1, Target, "\") > 0 Then
Target.Select
MsgBox "The following symbols are not allowed in this
field! ""# < > ? [ ] : * \ and /"" "
Target = ""
End If

If Union(Target, Me.Range("AH3,I11,T11")).Address =
Me.Range("AH3,I11,T11").Address _
And InStr(1, Target, "/") > 0 Then
Target.Select
MsgBox "The following symbols are not allowed in this
field! ""# < > ? [ ] : * \ and /"" "
Target = ""
End If


Application.EnableEvents = True
End Sub

-----Original Message-----
I pasted your code into a sheet module and couldn't reproduce the problem.

I did add a line to turn events back on at the end.

--
Regards,
Tom Ogilvy




.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Ambiguous name detected: worksheet_change 2
Date Stamp 4
From Excel to user form 0
Automatically Copy Information when data is entered 2
Worksheet_Change Woes 6
"ElseIf c.Value = "X_Clear" Then" and "...Target.Cells.Count > 1"don't work 4
Need a accumulator driven from second cell 4
Help with Sub's on a worksheet 4

Top