Microsoft KB Archive/215166

= XL2000: Sheets(Array).Select Statement Does Not Select All Sheets in the Array =

Article ID: 215166

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q215166



SYMPTOMS
When you programmatically select all of the sheets in an Excel workbook as an array, only the activated sheet gets selected.

Example
The following example illustrates this behavior.  Start Microsoft Excel, and create an Excel Wordbook with five sheets named Sheet5, Sheet4, Sheet1, Sheet2, and Sheet3 respectively.

NOTE: To add worksheets, click Worksheet on the Insert menu. Press ALT+F11 to start the Visual Basic editor. On the Insert menu, click Module.  Type the following code in the module sheet: Sub test Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")).Select Sheets("Sheet3").Activate End Sub  Press ALT+F11 to switch to the Excel workbook. Press ALT+F8 to open the macro list, click test under Macro name, and then click Run. Only Sheet 3 is selected.

<div class="cause_section">

CAUSE
This problem occurs because the sequence of the sheets named in the macro does not match the sequence of the sheets as they appear on the Excel workbook, and the sheet that is activated is not the first sheet in the array. This problem only occurs when you attempt to programmatically select all of the sheets in a workbook. When you programmatically select some the sheets in a workbook, the sequence of the worksheets is not important.

<div class="resolution_section">

RESOLUTION
To resolve this problem, make sure that the sequence of the sheets in the macro matches the sequence of the sheet numbers on the Excel workbook, and ensure that the sheet that is activated is the first sheet in the array.

For example, if you change the code in the example macro in the "Symptoms" section to the following Sub test Sheets(Array("Sheet5", "Sheet4", "Sheet1", "Sheet2", "Sheet3")).Select Sheets("Sheet5").Activate End Sub the macro works as expected. All sheets are selected and sheet 5 is activated.

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Additional query words: XL2000

Keywords: kbbug kbpending KB215166

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.