Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Dec 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Questions re: tabbing in a protected worksheet (2002)

    1. I am unable to tab to the second cell/field in a protected worksheet, but I am able to use the left arrow key to return to it. I verified that the cell is not locked.

    2. Why am I not able to use Shift+Tab to reverse tab direction?

    3. Is it possible to use a macro to set tab order from left to right in the first row, then down one column and down another column? I was able to do this when my form was a Word template.


    Thanks for any assistance with these questions.

    Eric

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

    Re: Questions re: tabbing in a protected worksheet (2002)

    Welcome to Woody's Lounge!

    1/2. There's something strange going on here. In a protected worksheet, Tab should cycle through the unprotected cells, moving from left to right and from top to bottom. Shift+Tab ought to cycle through the unprotected cells in the reverse direction. Is it possible that you have used OnKey to change the behavior of Tab and Shift+Tab?

    You can restore the original behavior of Tab and Shift+Tab by typing the following in the Immediate window in the Visual Basic Editor, followed by Enter:

    For Tab: Application.OnKey "{TAB}"

    For Shift+Tab: Application.OnKey "+{TAB}"

    3. It might be possible, but it's not trivial. Using Tab to move right and Enter to move down is a lot easier, especially if you disallow selecting locked cells in the Tool | Protection | Protect Worksheet dialog.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Questions re: tabbing in a protected worksheet (2002)

    There might also be a Selection Change event routine that is changing the action.
    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Dec 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Questions re: tabbing in a protected worksheet (2002)

    Here's an update regarding my problems:

    1. I discovered that I had a hidden column before the skipped cell. Removing the column allowed me to tab to the desired cell.

    2. Hans, I'll pass on your instructions for navigation to the folks who will use the form. Though it would be nice to have a tab order macro, this will do for now. Thanks.

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Questions re: tabbing in a protected worksheet (2002)

    The way to do this is as Hans suggested: protect all of the cells on the sheet except the data entry cells, then protect the worksheet. I'll spell it out slower:
    1. <LI>Use <Ctrl>A to select all of the cells
      <LI>Menu, Format | Cells | Protection Tab | make sure Lock is fully checked. Note, a slightly gray checkmark means some are protected & some are not. Click on the checkmark until it is a black checkmmark.
      <LI>Select all of the cells where users may enter data: click on the first cell then Ctrl-Click on the rest of the cells. If there is a column where they can enter data, then it will be easier to select the column first, then individual cells: the order is not important.
      <LI>Menu, Format | Cells | Protection Tab | click on the Lock checkbox to clear the checkmark.
      <LI>You can repeat steps 3 & 4 for a single cell, if you forget one.
      <LI>Menu, Tools, Protection, Protect Sheet. I would not use a password, you are only using protection to aid navigation.
      <LI>Now <tab> will take you to the "next" place to enter data, where next is defined as right then down. <Shift><Tab> will take you to the previous entry.
      <LI>I usually fill each of the data entry cells with the lightest (least distracting) color, tan is good; and I draw a border around each cell. Now, most users will expect <tab> to take them to the next colored cell.
    HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Questions re: tabbing in a protected worksheet (2002)

    Hello; welcome to the Lounge, and thanks for the afternoon's entertainment.

    I believe that the attached workbook does what you wish. It operates on Sheet1, and causes the Tab to select A1 through L1 (one cell per Tab key operation), then repetitively cycle through:
    <UL><LI>B2 - B6
    <LI>D2 - D6
    <LI>E2 - E6
    <LI>F2 - F6
    <LI>L2 - L6
    <LI>H2 - H6[/list]The project is not locked, so you can review and revise the code to your particular needs.

    The design approach is provided immediately below.

    <pre>'############################################ ############################
    '# Purpose: To cause the Tab key to cycle: #
    '# #
    '# a. Across row 1 from Columns m to n where m and n are #
    '# predefined in the code, then #
    '# #
    '# b. Down a selection of columns from row 2 to x where x is #
    '# predefined in code #
    '# #
    '# ################################################## ################## #
    '# Arguments: none #
    '# #
    '# ################################################## ################## #
    '# Entry Conditions: #
    '# #
    '# If the worksheet is protected; the contiguous range including the #
    '# complete selection sequence path and the cells immediately to the #
    '# right must not be locked. #
    '# #
    '# ################################################## ################## #
    '# Exit Conditions: #
    '# #
    '# ################################################## ################## #
    '# General approach: #
    '# #
    '# a. On opening the file, the Worksheet of interest will be #
    '# activated, Cell A1 will be selected, and the named #
    '# values of PrevRow and PrevCol will both be set to 1 ( #
    '# the co-ordinates of Cell A1) #
    '# #
    '# b. The worksheet will contain code that fires the ReschTab. #
    '# Main procedure on a change of selection #
    '# #
    '# c. The ReschTab module will #
    '# #
    '# i. Initialize #
    '# #
    '# 1. Populate arrays determining the intended path of #
    '# the selection sequence #
    '# #
    '# ii. Relocate #
    '# #
    '# 1. Compare the current location with the previous #
    '# location, and if it is in the same row but one #
    '# column to the right of the previous location and #
    '# the previous location was in the selection path #
    '# #
    '# a) Select the next cell in the path selection #
    '# sequence. #
    '# #
    '# iii. Record #
    '# #
    '# 1. Set the Named Values to the new co-ordinates. #
    '# #
    '# ################################################## ################## #
    '# Tips and Gotchas: #
    '# #
    '# If you want to select a cell immediately to the right of the #
    '# current selection; select an intermediate selection first. #
    '# #
    '# #
    '################################################# #######################
    </pre>

    Regards
    Don

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Questions re: tabbing in a protected worksheet (2002)

    Having allowed the code from my last post to mature for 24 hours, I note that:
    1. <LI>Where I specified that the starting point in Row 1 would be programmable; I fixed it at column A. This is a minor problem and can be easily rectified if necessary
      <LI>Although the area through which the active cell cycles cannot be protected, the same effect can be achieved by simply replacing the user's entry with the original entry when leaving the cell. However I am a little confused regarding the workbook's protection scheme.
      <UL><LI>Can you advise on which cells the user will be allowed to change?
      <LI>Is it those cells that they would Tab through?
      <LI>Will the first row be treated differently than the columns with respect to protection?
    [/list]
    Regards
    Don

Posting Permissions

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