Restrict User Input from inputting non-ASCII characters

[optimus]

How do I restrict non-ASCII keystroke inputs on all cells of a worksheet. The cell should only accept ASCII characters.

Is this even possible?

Supposedly, "KeyPress" event can do the trick, however "KeyPress" is only for objects. Selecting worksheet doesnt have the "KeyPress" event.

This is a sample.

---------
|I | (initial)
---------
|AI | (user type "A")
---------
|AbI | (user type "b")
---------
|AbcI | (user type "c")
---------
|AbcI | (user type "@" but rejected)
---------
|AbcI | (user type "@" but rejected)
---------
|Abc1I | (user type "1")
---------
|Abc12I | (user type "2")
---------

[ADezii]

I am not sure if you can capture 'individual keystrokes' in a Cell, but once a Value is written within a Cell you can easily remove any unwanted keystrokes and return the filtered value. The following Code, in the Worksheet's Change() Event will analyze the Values in a Cell once the User has left it, and return only Capital Letters between A-Z:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim varCellValue As Variant
Dim intLen As Integer
Dim intCharCtr As Integer
Dim strBuild As String
Dim rngRange As Excel.Range
 
Set rngRange = Target
 
varCellValue = Target.Value
 
If IsNull(varCellValue) Then Exit Sub
 
intLen = Len(varCellValue)
 
For intCharCtr = 1 To intLen
  Select Case Asc(Mid(varCellValue, intCharCtr, 1))
    'Only allow Capital Letters A to Z
    Case 65 To 90
      strBuild = strBuild & Mid(varCellValue, intCharCtr, 1)
    Case Else       'do nothing
  End Select
Next
 
rngRange = strBuild
End Sub

[NeoPa]

As far as I can tell (and I checked so I'm pretty confident of this ;-)) there is no facility within Excel to capture key strokes within either a workbook or a worksheet.

A couple of things to bear in mind re ADezii's code :

1. The Target parameter is an Excel.Range object. They can take both extended ranges (A5:C12) as well as multiple ranges in a list (A5:C12,P7,R3:S7). The code doesn't currently handle that, but it could be extended and does illustrate the concept you would need to use to handle your data input.
2. The Select Case on line #17 needn't include the Asc() part as long as line #19 says Case "A" To "Z"
   This also makes the code clearer.

The code is there as an illustration of what can be done. Equally, you could replace each unacceptable character with another instead if you preferred, an asterisk (*) for instance, simply by putting that in the Case Else section.

[zmbd]

which version of Excel/Office and what OS are you working with?
Also... all characters from dec000 thru dec255 are recognized under the extended ASCII guidelines. You need to be more specfic about the range:
http://www.ascii-code.com/
We're only assuming you mean to restrict between dec32 and dec127

[ADezii]

1. The Range Object to which you are referring represents a single Cell. Once a Value is entered into any Cell, the Change() Event is fired. I don't see how a Range, outside of a single Cell, is relevant here.
2. The key point in this Thread is the restricting of Non-ASCII characters, some of which have no visible representation. In my opinion, you have to deal with the ASCII characters themselves (65 - 90) as opposed to what they represent (A - Z).
3. Of course, I could completely be off in my interpretation of the Question (LOL).

[zmbd]

@ADezii: I think you're pretty much on with the intrepretation you have made.

It's just that I am a very oldschool programmer (think punch-cards and COBAL and a teletype terminal --- ah the days of 1K programming (@.@)) and, as you have mentioned, the term ASCII refers to a very large amount of information.

@optimus

IMHO, you need to tell us the true nature of the question.

However, using the same inference that ADezii made and the examples shown, I suspect that you are asking to restrict the entry to the alphanumeric range.

There is a way to do this using data-validation and a custom function; however, you need to do two things:
1) Tell us which version of Excell/Office is being used
2) Confirm the ASCII span to the alphanumeric, or any printable character, etc... Because, as written, you are mis-using the ASCII term - hence why I provided the link to the table of ASCII values.

Programming terminology is like Chemistry... the details are importaint and when misused may result in very unintended results!

[NeoPa]

@ADezii.

#1 - Not true, I'm afraid ADezii. Try the following :

1. Select the range I specified (A5:C12,P7,R3:S7).
2. Enter the keystroke "X".
3. Use Ctrl-Enter to enter that value into each of the selected cells.

You'll see that Target doesn't refer to a single cell.

A more common situation is found when pasting blocks of cells.

#2 - While the wording of the question indeed states ACSII characters, the example shows that the wording is not correct, and even a little misleading. The example indicates that the OP needs alphabetic characters. In fact, a point I missed earlier, it doesn't even indicate they must be upper-case.

That said, I find it clearer to use the characters themselves in the code. This seems markedly clearer to my mind as to exactly what is being achieved. Nevertheless, if you see this as relating to the ASCII code set in some way (other than randomly), then I see where you're coming from, even if I don't believe it makes good sense from my perspective. It works as an illustration of the concept anyway - and that's the most important thing.

#3 - Interpreting questions is often extremely difficult. In this case we're only talking minor details. Worth bringing up for completeness, and for expanding the understanding of any readers, but certainly not to be seen as criticism. When the details of a question need to be inferred rather than simply read, it makes it all the more difficult to get them all right every time.

@Optimus.

You should not take this discussion of the flaws in your question as criticism either. It's a first post and what we're talking about is not something everyone would even be aware of. Some of us have been dealing with such details for many years and can be expected to know the ins and outs in more detail. That level of expectation would not cover a member of the general public.

[zmbd]

Neopa: "Use Ctrl-Enter to enter that value into each of the selected cells"

I forget about those silly array entries too... think that I wouldn't though given how often I use the statistical array formulas!

Neopa: @Optimus.
You should not take this discussion of the flaws in your question as criticism (...)

Here-Here and a second on that comment Optimus!
Rarely should any comment here be taken as anything more than "constructive" in intent with the goal to either clarify the goal of the question or provide some insight and point to a better way of doing something. (in fact, I had to learn this myself (^-^) when I started posting here and one of the reasons I keep coming back!)

[ADezii]

@NeoPa:
One of us is missing the point, and it is probably me (LOL). If you duplicate your steps exactly: selecting a Range, entering a Value within the Range, then CTRL+ENTER to copy that Value to the other Cells within that Range, the Change() Event will fire but will only evaluate and return the proper result in the Cell containing the original Value. This is what I meant by Target referring to only a single Cell. Make sense, or not?

[NeoPa]

It seems you may not be following my instructions correctly ADezii.

If you create a new workbook and put the following code behind "Sheet1" :

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
    Call MsgBox(Target.Address)
End Sub

Now follow the instructions exactly as stipulated. The "X" is NOT entered in step #2, it is simply keyed and left until step #3 to enter in any cells.

You should see a message box with the following :

$A$5:$C$12,$P$7,$R$3:$S$7

Alternatively, try copying and pasting a block of cells into that worksheet. Again, you'll see that Target is not a single cell. The more complicated scenario was simply to illustrate the full range of possibilities for the Target parameter.