Need to develope key press event in Excel 1

[butchkmd]

I need to develope a keypress event in Excel where a value typed into a cell can be evaluated without pressing enter or tab or anything. I have a loop the evaluates the
len(activecell.value), if 1 it moves to the next cell and sends focus to the worsheet selectionchange_event where I call the worksheet activation event and it starts all over again. The trouble is you have to press enter or tab to give the cell the value how do I get around this?


Thanks
Matt

 

[EndoMan]

There is not a "Key Press" event available in Excel, not even in the Application Events. However, you could possibly use Data Validation on the cell. One of the options in Data Validation is to set text lengths. They would still need to hit ENTER or TAB, but it won't let the user leave an invalid value in the field. Additionally, they won't need to enable macros for it to work.

 

[butchkmd]

I saw that... you are correct...it doesn't work.
ugh

 

[EndoMan]

Although you can use the SelectionChange event and use Target.Select to go back to the cell which did not meet your LEN criteria. You can easily then select another cell without causing the macro to run.

I presume by "it doesn't work" that you mean data validation?

If you want to get fancy, you could get a Userform to pop up whenever the user selects a particular cell. With a textbox on the userform, you could use the KeyPress event of the textbox to trigger the evaluation. However, be carefull how you write the code. Remember that this code will run for each and EVERY key stroke. So if you are looking for an exact LEN (i.e., 4) using an IF statement, it will run for each keystroke.