Limit a cell to numeric input only

August 18, 2012 at 00:17:44
Specs: Windows 7
I would like to ask for help with the following:

I would like to restrict the cell input to numeric input only. I do not want to use data validation to do this, because from what I see data validation only works when focus is lost on the cell. I want the keyboard letters disabled so that only numeric input can be made in the cell while that cell has focus. When the cell is on focus and I hit a letter key on the keyboard, I want nothing to happen.

In addition, I would like the length of the cell input value to be limited. For example, I have a cell that has a valid entry range of three digits. I want the program to allow only a three digit entry. Once the third digit is entered, I want further input to not be possible.

I am very much a novice at VBA/macros, so the more remedial the answer, the better.

Any help would be appreciated. Thank you all very much.


See More: Limit a cell to numeric input only

Report •

August 18, 2012 at 19:37:21
This one's easy...

There is no way for Excel to "disable" keyboard entry of letters. The only way to allow numbers only is to check the cell after the entry is made, the very thing that you don't want to do.

Whether you use Data Valdation or VBA, no check will be done until the value is entered into the cell and Excel and/or VBA has a chance to evaluate the entry.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

August 18, 2012 at 20:15:03
I don't know much about VBA, but I find that hard to believe. Years ago, when I used BASICA, you could "disable" keyboard keys by ASCII code.

You can't do that (or similar) in VBA? I know you can in Visual Basic. I just don't know how to do it.

Thanks so much...


Report •

August 19, 2012 at 04:55:53
I stand slightly corrected. I had not considered using the OnKey feature to output an empty string for every key. An empty string is represented by "". That doesn't exactly disable the keyboard, it uses VBA to output something different (nothing, more or less) when a key is pressed.

The code at this site breaks the keyboard down into sections, so if you comment out the section that "disables" the number keys, those should be the only ones that work.

Take a look here..I have not tried it.

Now, I have found that OnKey can be erratic, but that's usually when you are trying to send a "list" of Onkey strings to simulate a number of commands, one right after the other. For individual key presses, it might be fine.

As far as limiting the entry to 3 numbers only, that will take a little more investigation. I'll see what I can find.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

Ask Question