using a single cell for data entry

[imatomic]

Basicaly what I am trying to accomplish is the use of a single cell to enter the values 1 thru 60, so starting at e2 for instance, if I enter the number 1 then cell f2 will populate with a 1, if I were to enter the number 18 then the cell w2 would populate with an 18. So the range of cells that need to populate in this example would be (f2:BM2) for row 2. Furthermore if I were to enter a -1 or a -18 in cell e2 then those numbers would be eliminated from the appropriate cells on row 2. The next 299 rows would need to perform the same way for a total of 300 user input rows.

The way that I'm imagining e2 would behave in the example would be...type a 1 then enter....18 then enter....31 then enter and so on. Columns f thru bm would locked and unselectable....so a tab should send me back to a3.

Reasoning.... this is a simple scoring system based on 60 codes and data integrity is very important and I want to eliminate as much human error as possible. I will be setting autofilters and such later on.

I am attaching a screencapture as well.

I have been fighting this for a few weeks now. I really hope someone can help me out on this.

Much thanks,
BC

[Leith Ross]

Hello imatomic,

Here is worksheet event macro that checks if the user input a value into cells E2 through E301. You can change the range if you need to. It is marked in red.

Please Login or Register  to view this content.

How to Save a Worksheet Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
3. Left Click on View Code in the pop up menu.
4. Paste the macro code using CTRL+V
5. Make any custom changes to the macro if needed at this time.
6. Save the macro in your Workbook using CTRL+S

[JBeaucaire]

Right-click on the sheettab where you want this to be going on, and select VIEW CODE.

Paste in this worksheet_change macro code:

Please Login or Register  to view this content.

[JBeaucaire]

Leith's macro (which I like better than mine, more thorough) merged with mine to accomplish all the OP requested:

Please Login or Register  to view this content.

[imatomic]

Thanks all for the help on this I will give it try and report back.

[imatomic]

JBeaucaire, Thanks so much! The last bit of code worked great but the only thing is....for instance....if were entering multiple numbers on the same row, E2 say 11,18,32,59 the cell selection would need to stay at E2 upon enter and then TAB would send you back to A3 when all data is entered. Other cells on the sheet would need to react normaly to an enter key being pressed. Just column E would exibit this behavior. Complicates it a little.....

[JBeaucaire]

Make this change:

Please Login or Register  to view this content.

That line of code is in two places, change it in both.

As for the TAB thing, only way I know to do that is with sheet protection on all the cells except columns A:E. When you tab through the cells it would jump from E back to A.

[imatomic]

This is what I ended up with....seems to work beautifully and will TAB back to A column.

Please Login or Register  to view this content.

Thanks for all of the help!
BC

[JBeaucaire]

On the sheet protection thing, since you're using macros anyway, you only need ONE line in your macro to give the macro permission to make changes on your protected sheet.

Please Login or Register  to view this content.

Note...if you're going to keep automating on this sheet, you'll have to keep setting that UserInterfaceOnly:=False flag...so you might want to just do it once and for all in a workbook_open event macro.

Please Login or Register  to view this content.

With that inserted into the ThisWorkbook module, it runs when the book first opens and your other macros won't all need to do it themselves.

Alternately, another worksheet event macro that goes in the sheet module(s):

Please Login or Register  to view this content.

Just some parting thoughts.

[imatomic]

Very good! I will work that in.

Thanks so much!

BC