| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"delete" key activates Sub Worksheet_Change
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"delete" key activates Sub Worksheet_Change
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 "Dave" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"delete" key activates Sub Worksheet_Change
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 "Dave" wrote in message ... 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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"delete" key activates Sub Worksheet_Change
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 wrote in message ... 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 "Dave" wrote in message ... 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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| Excel "Move or Copy" and "Delete" sheet functions | Excel Worksheet Functions | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| How do I make a "Worksheet_Change event" to show any changes to cells? | Excel Worksheet Functions | |||
| Worksheet_Change Event "Circular Reference" | Excel Programming | |||
![Dave[_35_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
