Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Posts
    134
    Thanks
    1
    Thanked 0 Times in 0 Posts

    tabbing in an Excel form (Excel 2003 sp 3)

    I need to create a simple form that is just a few columns where data is entered into several rows. I would like to be able to tab through the rows and automatically return to the first field (cell) after I reach the final field (cell). I know I've created something like this before, but I just can't remember the process. I've been trying to search for the answer, but I'm not even sure what to search on. Any assistance you can give me with this will be appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: tabbing in an Excel form (Excel 2003 sp 3)

    Select the cells you want to use as input cells.
    Select Format | Cells...
    Activate the Protection tab.
    Clear the Locked check box.
    Click OK.
    Select Tools | Protection | Protect Sheet...
    Clear all check boxes except "Select unlocked cells".
    Click OK (you can specify a password if you like).

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: tabbing in an Excel form (Excel 2003 sp 3)

    There are two ways you can do this: One is changing the scroll area of the sheet and the other is using sheet protection on locked cells.

    Scroll Area Method:
    - Right click on the sheet tab you want to modify
    - From the context (right click) menu, choose View Code
    - When the VB Editor opens, locate the properties window. (Press F4 if you do not see it!)
    - Locate the ScrollArea property and type in the reference for your form.
    See the screen shot below!
    This will lock the rest of the spreadsheet and you can only tab (or enter) within that range. (It also locks the sheet scrolling)

    Protection Method:
    - All cells are locked by default, so they get affected when you assign sheet protection. To start the procedure do the following:
    - Select the range on the relevant sheet where your form will be located
    - Choose Format | Cells to activate the dialog
    - Click the protection tab
    - De-activate "Locked" and choose OK
    - Choose Tools | Protection | Protect Sheet (I cannot remember the exact words, I am on Excel 2007...No menus!)
    - In the dialog, assign an optional password, de-activate "Select Locked Cells" and choose OK
    The entire spreadsheet will now be protected, except the block you highlighted. There you can still tab and enter and type.

    Experiment with both options and choose the one that suites your situation best!
    Attached Images Attached Images
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: tabbing in an Excel form (Excel 2003 sp 3)

    The ScrollArea property of a sheet is not saved with the workbook, so you must set it when the workbook is opened, in the Workbook_Open event procedure in the ThisWorkbook module:

    Private Sub Workbook_Open()
    Worksheets("Sheet1").ScrollArea = "A1:C5"
    End Sub

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: tabbing in an Excel form (Excel 2003 sp 3)

    I new that....but forgot that!!!! <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> Thanks for adding this important bit of detail!
    Cheers
    Regards,
    Rudi

Posting Permissions

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