XL5: Can't Record Resetting Color Palette in Visual Basic

• Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel 5.0, when you run a recorded Visual Basic, Applications Edition, module that resets the color palette, the following error message is displayed:

   Run time error '1004':
   Workbooks method of Application class failed

CAUSE

There is no direct method in Visual Basic to reset the default color palette. The method for resetting the default color palette is to set the workbook from which colors are used to none. The result is that no workbook name is given, and "" is the argument passed to Workbooks.Colors. Because "" is not a valid workbook name, the error message is generated.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel for Windows version 5.0c.

WORKAROUND

To reset the default color palette, you must use the Microsoft Excel 4.0 COLOR.PALETTE() macro function, as in the following example:

   Sub ChangeColorPalette()
      ExecuteExcel4Macro "COLOR.PALETTE("""")"
   End Sub