Some Cells in Worksheet Not Selected Using TAB or SHIFT+TAB (178683)


The information in this article applies to:

  • Microsoft Excel 97 for Windows
  • Microsoft Excel 98 Macintosh Edition
This article was previously published under Q178683

SYMPTOMS

If you press either the TAB key or SHIFT+TAB to move from cell to cell in a worksheet, some cells in the worksheet may not be selected, even though they are unlocked.

CAUSE

This problem may occur when the following conditions are true:

  • The worksheet contains one or more merged cells. The merged cells, as well as other cells, have been unlocked through the following steps:

    1. Selecting the cells.
    2. On the Format menu, clicking Cells, and then selecting the Protection tab.
    3. Clicking to clear the Locked check box, and then clicking OK.-and-

    4. The worksheet has been protected by clicking the Tools menu, pointing to Protection, and clicking Protect Sheet.-and-

    5. The Visual Basic EnableSelection property of the worksheet is set to 1, or xlUnlockedCells.-and-

    6. The cells that are not selected are adjacent to the merged cells.

RESOLUTION

To prevent this problem from occurring, set the EnableSelection property of the worksheet to zero (0), or xlNoRestrictions. To do this, follow the steps in either Method 1 or Method 2.

Method 1

  1. On the View menu, point to Toolbars, and click Control Toolbox.
  2. Click the Properties tool in the Control Toolbox.
  3. In the list of objects, select the worksheet (for example, Sheet1).
  4. In the list box to the right of EnableSelection, select "0 - xlNoRestrictions".
  5. Close the Properties window.

Method 2

  1. On the Tools menu, point to Macro, and click Visual Basic Editor.
  2. If the Project Explorer window is not visible, click Project Explorer on the View menu.
  3. In the Project Explorer, select the name of the worksheet (for example, Sheet1).
  4. On the View menu, click Properties Window.
  5. In the list box to the right of EnableSelection, select "0 - xlNoRestrictions".
  6. On the File menu, click "Close and Return to Microsoft Excel."
You should now be able to use the TAB key and SHIFT+TAB to select all of the unlocked cells in the worksheet.

NOTE: You can also use the pointer to select unlocked cells in a worksheet, even if the EnableSelection property is set to xlUnlockedCells.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

You can merge multiple cells in a worksheet into a single cell by following these steps:

  1. Select the cells that you want to merge.
  2. On the Formatting toolbar, click the Merge And Center tool. Or, click Cells on the Format menu, select the Alignment tab, click to select the Merge Cells check box, and click OK.
When you protect a worksheet, you can use the TAB key and SHIFT+TAB to select the unlocked cells in the worksheet, one cell at a time in order. However, if the EnableSelection property of the worksheet is set to xlUnlockedCells, cells that are adjacent to merged cells may not be selected.

The following steps demonstrate this problem:

  1. In Microsoft Excel, create a new workbook.
  2. In Sheet1, select cells B2:D4. On the Formatting toolbar, click the Merge And Center tool.
  3. Select cells B2:E5. On the Format menu, click Cells. Select the Protection tab. Click to clear the Locked check box, and click OK.
  4. On the Tools menu, point to Protection, and click Protect Sheet. Click OK.
  5. Select cell B2. Then, press the TAB key or SHIFT+TAB repeatedly.

    NOTE: Each unlocked cell in the worksheet is selected as you press either the TAB key or SHIFT+TAB.
  6. On the Tools menu, point to Macro, and click Visual Basic Editor.
  7. If the Project Explorer window is not visible, click Project Explorer on the View menu.
  8. In the Project Explorer, select the name of the worksheet (for example, Sheet1).
  9. On the View menu, click Properties Window.
  10. In the list box to the right of EnableSelection, select "1 - xlUnlockedCells".
  11. On the File menu, click "Close and Return to Microsoft Excel."
  12. Select cell B2. Then, press the TAB key repeatedly.

    NOTE: Cells E3 and E4 are not selected; the selection jumps directly from cell E2 to cell B5.
  13. Select cell B5. Then, press SHIFT+TAB repeatedly.
NOTE: Cells E2 and E3 are not selected; the selection jumps directly from cell E4 to cell B2.
Modification Type:MinorLast Reviewed:8/19/2003
Keywords:kbbug KB178683
©2003 Microsoft Corporation. All rights reserved.