Excel 97 – Restrict data entry

Topic

New Reply

This morning someone asked me a qeustion, and I have pondered it quite a bit. Thought I’d throw it to you to see if you can help. He has a spreadsheet that has data in columns A-D. He has some severely computer-impaired data entry clerks doing data entry. He wants to set something up whereby once a clerk has entered info in Column D and presses the tab key, the cursor will return to the next row in Cell A (instead of moving to column E).

He doesn’t want to use code. Any ideas?

Reply | Quote

Replies

If you select rows A to D, and go to Format, Cells and select the Protection tab. Clear the lock check box. then make sure tha columns E onwards have the locked ticked. Then protect the sheet. Now when the tab key is pressed in column D, you should be taken to column A. If that method id not suitable there are other ways.

Andrew

Reply | Quote

That sorta works. But, it takes me back to column A in the same row. I need to go down a row so I’m not overwriting info I entered in that row.

Reply | Quote

Go to Tool, Options, and in the Edit tab tick the Move Selection after Enter, and set the direction to down.

That should do it for you.

Andrew

Reply | Quote

That’s close, Andrew. Thanks for the ideas. But, what he wants is to allow the clerks to use the TAB key to navigate all the time so that they don’t accidentally forget to hit enter when they are in the last column. Either that, or set it to use the Enter key all the time. HE said he doesn’t want them having to remember to switch they key from Tab to Enter on the last entry.

Reply | Quote

I am not sure what the problem is – pressing tab on the last cell should enter the data and move to the first cell. More likeky, it will progress to a blank cell, unless all rows are being used or only the used roews are unprotected. What is happening on the last entry?. I think any more comprehensive solution would require some code, which your friend would prefer to avoid.

Andrew

Reply | Quote

After entering data in Cell D2, when I press the TAB key, it goes to A2, when I want it to go to A3.

Reply | Quote

You need to make sure that all cells that you want entries for are unprotected. The tab key always moves you on to the next cell.
Is it possible to post an example of the worksheet ?.

Reply | Quote

Ok. It seems to work to a point. I’ve included a dummy spreadsheet where I used the tab to enter the numbers. Where I ended is where it started jumping back to A1 instead of going to the next row.

Reply | Quote

You are right – it works up to point, that point being the last used cell as far as Excel is concerned. One workaround might be to make some sort of entry in the last row that you think will be required and that will allow you to proceed. A better suggestion is to use just one line of code and switch off protection. The code restricts the scroll aerea to columns A to D, but requires to be run each time the book is opened. This could be done automatically by placing the following code in the ThisWorkbook object

Private Sub Workbook_Open()
    ActiveSheet.ScrollArea = "A:D"
End Sub

I am attaching the sheet with the code in an ordinay module and a button on the sheet to switch it on. No need to use sheet protection with this method.

Andrew

Reply | Quote

Hey. I think you’ve got it! Thanks. I tried it and it did just what I was looking for. Now, If I understand this correctly, I need to run this (push the button)each time I load the spreadsheet? Or can I include it in some start up option so it automatically loads with the spreadsheet and is hidden to the user? (Just when you thought you were done with me!!)

Reply | Quote

I am attaching another worksheet that automatically sets the restriction for sheet1. If you need more than one sheet just copy it.

Andrew

Reply | Quote

Thanks so much for all your help, Andrew. It works perfectly. Just what he asked for!

Reply | Quote