Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 97 - Restrict data entry

    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?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel 97 - Restrict data entry

    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

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97 - Restrict data entry

    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.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel 97 - Restrict data entry

    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

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97 - Restrict data entry

    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.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel 97 - Restrict data entry

    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

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97 - Restrict data entry

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

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel 97 - Restrict data entry

    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 ?.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97 - Restrict data entry

    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.
    Attached Files Attached Files

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel 97 - Restrict data entry

    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<pre>Private Sub Workbook_Open()
    ActiveSheet.ScrollArea = "A"
    End Sub</pre>

    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
    Attached Files Attached Files

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97 - Restrict data entry

    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!!)

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel 97 - Restrict data entry

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

    Andrew
    Attached Files Attached Files

  13. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97 - Restrict data entry

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •