Restrict cells to numeric input

[Mark1011]

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.

Mark1011

[jason.b75]

If not impossible then it's close to.

Use a worksheet selection change event to open a userform with a texbox, then validate the entry on that.

See post #5 here for validation code.

[Mark1011]

Dear jason.b75,

Thank you for this. I pasted the code in a macro, and nothing happens.

I'm sure that I'm doing something wrong.

In reading the thread, it sounds like the fellow in the thread you directed me to wanted to do exactly the same thing I'm doing.

If you don't mind a little more remedial help, I'd appreciate it. I would understand if you don't have time to be a VBA teacher.

Thank you again.

Respectfully,

Mark1011

[jason.b75]

Hi Mark,

You can't use the code directly with the worksheet, the way you wanted to do this isn't possible. I'm short on time at the moment, but will check back later to see how you're getting on and provide more detailed answers if nobody else does beforehand.

What you need to do is create a userform with a textbox. link to guide

You would use a selection change event to open the form, link to guide

With the code in the link from my previous reply in the userform code, used to validate the textbox, not the cell in the worksheet.

Hope that makes some sense.

[Mark1011]

Dear jason.b75,

I will try the links you suggest.

Thanks...I really appreciate the help. I'll post here again and let you know if I had any luck.

Appreciate the patience and assistance.

Respectfully,

Mark1011

[Mark1011]

Dear jason.b75,

I had limited success.

I was able to create a text box like your first link described. It was cool, but I have no idea what to do with it. All I could do with it was type in a name, and then close it with either the escape button or the "Close Form" command button I created. The 2nd link regarding selection change event...I was completely lost.

I put my list of names in a worksheet named CrewNames. The CrewNames worksheet is a different worksheet than the one I want to load the names in to. How do I:

1. Get the text box to appear in the "Name" cell of the worksheet that I want to enter the names on?
2. Tell the text box where to look for the name that I'm typing in?
3. Place the code you gave for autofilling in the manner I described using letters as a filter?

Sorry, man...like I told you, I know about as close to nothing as you can get about VBA.

Thank you for trying to help.

Sincerely,

Mark1011

[jason.b75]

Mark,

Can you attach a sample workbook to show what you're working with, and what you need to happen?

The links I gave you earlier explained what you needed to use, but it had a lot of extra information that wasn't strictly relevant to what you're doing.

With a sample sheet I can add the form and code tailored to match your sheet, hopefully that would be easier for you to follow if and when you need to make changes to it.

[Mark1011]

Hi jason.b75,

Thank you for your response. I'm not sure how to attach a sample workbook, but perhaps I can describe what I want to happen. What I want to do is pretty straightforward.

I have written a spreadsheet that calculates aircraft weight and balance data for a Boeing 737. There are several inputs that require numeric data only, and two inputs that require upper case alphabetic input only. Those inputs include:

1. Flight number. This is a 3-digit number. No alphabetic inputs.
2. Number of passengers. This is a number that is a maximum of 3 digits. No alphabetic inputs.
3. Fuel, kilograms of baggage, etc. All numeric-only inputs with various maximum number of digits. Kilograms of baggage will be limited to 4 digits, fuel will be limited to 5 digits. Again...no alphabetic input.
4. There are 2 cells in which I want to allow upper case alphabetic input only. These are the departure and destination airport codes. Each are 4 alphabetic characters in length.

In the cells requiring numeric input, I would like to restrict the keyboard inputs to numbers only. If you press on a letter character, I want the computer to just not respond. When the digit limit is reached, I would also like the computer to stop responding. For example, if I want to enter 2000 kilograms of baggage weight in the baggage weight cell, if I try to type in a 5th digit, I want the computer to just ignore the input. If I try to key in a letter, I want the program to ignore the input. I don't need any warnings or message boxes...just ignore the input.

I would like the two cells that require alphabetic input to behave the same manner if you would try to key in a number or exceed 4 characters.

A hundred years ago, when I used to program using the BASICA version that was included in my DOS disk, you could limit the valid keyboard characters by defining which ASCII codes the program would respond to. I have to believe that there is still a way to do that using VBA.

If you need me to attach a sample workbook, I'll figure out how to do that, but like I said, what I want to do is pretty straightforward. Heehee...I just have no idea how to make it work like I want it to.

I really appreciate your trying to help me out.

Gratefully,

Mark1011

[jason.b75]

Mark,

In a way what you want is possible, but there is no way to make it practical reality.

You can use vba to disable keys as required, restrict the length of an entry, etc.

However there is a limitation in excel that makes this useless, vba has no effect on a cell while it is in edit mode (when the cursor is flashing in the cell).

This would mean that you could disable everything on the keyboard except numbers, the user could select a cell and it would only accept a numeric keypress, for the first character, once this first key is accepted and the cell enters edit mode, the vba becomes ineffective, allowing any keypress to be accepted, at this point any code to validate the length of the string will also be rendered useless, the code will not be active until the user presses enter to accept the entry and close the cell (leave edit mode). If the user double clicks the cell before entry then this would also enter edit mode, meaning not even the first character would be validated.

These 'problems' are the reasoning behind my suggestion of a userform and textbox, I'll try a few things to see what works best and post a sample sheet later.

Based on your last post, you could have a single form for all 4 inputs (or all inputs if there are others that don't need validating) which could then be written to the sheet as required.

[jaslake]

Hi Mark

Attached is an example of what you can do to restrict to Numeric Entry ONLY and to limit to three digits. The code is in the worksheet module (right click on the tab then select view code).

In the attached the code works on any cell...so...enter Alpha characters and see what happens. Enter numeric characters (of any length) and see what happens. The code CAN be restricted to specific cells or range of cells.

If you're interested in pursuing this approach attach a representative sample of your file describing what you'd like to happen where.

[Mark1011]

Hi jason.b75 and jaslake,

Thanks to both of you for your responses. Jason, that is an unfortunate characteristic of Excel that I wasn't aware of. It seems a textbox may indeed be the only way to do this. If I could ask your assistance in constructing textboxes the way that I want them to work, I'd be grateful.

jaslake, thank you for sending the worksheet and code. I tried it, and it works exactly like jason.b75 says, so unfortunately it's not exactly what I'm looking for.

Dumb question #63: I don't see how to attach a worksheet on this forum? It's probably right in front of me...

Thanks again to both of you.

Sincerely,

Mark1011

[Cutter]

To Attach a File:

1. Click on Go Advanced (bottom right of the Quick Reply area)
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[Mark1011]

Thanks, Cutter. Really appreciate it.

Sincerely,

Mark1011