How to Link an Excel Worksheet to a Macro Sheet

 Last reviewed: November 2, 1994
Article ID: Q32317
The information in this article applies to:
  • Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a, 5.0
  • Microsoft Excel for OS/2, versions 2.2, 2.21, and 3.0

SUMMARY

The information below tells how a macro places a calculated value from a macro sheet onto a worksheet. Before starting, you should know the following:

  1. How to write and execute a command macro.

  2. How to use variable names on a macro sheet using the =SET.NAME function.

  3. How to write and use a macro dialog box.

MORE INFORMATION

The macro in the example below brings up a dialog box and allows a numeric value to be entered. The macro then takes this entered value and places it on the worksheet.

You cannot have your worksheet get a value from your macro sheet by using a simple link. Your macro must go out and place the value on the worksheet. For example, try a sample dialog box range on a macro sheet named "Mine.XLM" in cells B6:H7, with the following entries. 

   B6:  6  C6:  0  D6:  0  E6:  0  F6: 200  G6:  50
   B7:  7  C7:  0  D7: 40  E7: 15  F7: 130  G7:  20
Note: The Init/Result cell in this case is cell H7 of the macro sheet. The code for a macro to place a value in cell A1 of a worksheet named "Yours.XLS" then reads as follows: 

   =DIALOG.BOX(B6:H7)
   =ACTIVATE("Yours.XLS")
   =SELECT(!A1)
   =FORMULA(Mine.XLM!$H$7)
   =RETURN()

REFERENCES

"Microsoft Excel Functions and Macros," version 2.x, pages 204-218

"Microsoft Excel User's Guide," version 3.0, pages 625-638

KBCategory: kbusage
KBSubcategory:

Additional words: 5.00 2.00 2.0 2.01 2.1 2.10 2.2 2.21 2.20 3.00
3.0 4.00 4.0

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.