XL5: Error Using ChangeLink Method with Assigned Procedure

• Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel, when you create an external link by assigning a macro in one workbook to a button in another workbook, and you use the ChangeLink method to change the source of the link, you receive the following error message:

   Run-time error '1004':

   ChangeLink method of Workbook class failed.

CAUSE

This behavior occurs when the procedure that uses the ChangeLink method is located in the workbook that contains the button. The problem only occurs when the workbook that contains the button is the active document, and the workbook that contains the linked procedure is open but has not been activated since it was last saved.

Consider the following example: The workbook ONE.XLS contains a procedure that is assigned to a button in TWO.XLS. The workbook TWO.XLS contains a procedure that uses the ChangeLink method to change the link to the button from ONE.XLS to another workbook. You receive the error message above if you save ONE.XLS, activate TWO.XLS and run the procedure to change the link.

Note that this problem does not occur when you use the ChangeLink method to change a link to a MS Excel 4.0 macro, or to a cell in another workbook.

WORKAROUNDS

To avoid receiving this error message, do any of the following:

• Manually change the link to the button using the Links dialog box.

  -or-

• Close or activate the workbook that contains the linked procedure before you run the macro to change the link.

  -or-

• Display either the Assign Macro dialog box, or the Macro dialog box before you run the macro to change the link.

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 version 5.0c for Windows.

REFERENCES

For more information about the ChangeLink Method, choose the Search button in Help and type:

    ChangeLink Method