Microsoft KB Archive/214337

Knowledge Base

XL2000: Some Cells in Worksheet Not Selected Using TAB or SHIFT+TAB

Article ID: 214337

Article Last Modified on 9/27/2003

---

APPLIES TO

• Microsoft Excel 2000 Standard Edition

---

This article was previously published under Q214337

SYMPTOMS

If you press either TAB 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 all of 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 clicking to select the Protection tab.
  3. Clicking to clear the Locked check box, and then clicking OK.

  -and-
  
  

• The worksheet has been protected by clicking the Tools menu, pointing to Protection, and clicking Protect Sheet.
  
  -and-
• The Visual Basic EnableSelection property of the worksheet is set to 1, or xlUnlockedCells.
  
  -and-
• 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 below.

Method 1

1. On the View menu, point to Toolbars, and then 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 box to the right of EnableSelection, click 0 - xlNoRestrictions.
5. Close the Properties window.

Method 2

1. On the Tools menu, point to Macro, and then 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 box to the right of EnableSelection, click 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.

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, click the Alignment tab, click the Merge cells check box, and then 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, and then click the Protection tab. Click to clear the Locked check box, and then click OK.
4. On the Tools menu, point to Protection, click Protect Sheet, and then click OK.
5. Select cell B2. Press TAB or SHIFT+TAB repeatedly.
   

NOTE: Each unlocked cell in the worksheet is selected as you press either the TAB key or SHIFT+TAB.

1. On the Tools menu, point to Macro, and then 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 box to the right of EnableSelection, click to select 1 - xlUnlockedCells.
6. On the File menu, click Close and Return to Microsoft Excel.
7. Select cell B2, and then press TAB repeatedly.
   

NOTE: Cells E3 and E4 are not selected; the selection cycles between cell E2 and cell B2.

1. Select cell B5, and then press SHIFT+TAB repeatedly.
   

NOTE: Cells E2 and E3 are not selected; the selection cycles through cell E4, B2, E5, D5, C5, and then jumps back to B5.

Additional query words: XL2000

Keywords: kbprb KB214337

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.