Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Apr 2003
    Location
    Morgantown, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Macro (2002/03)

    Hello,

    I've got a workbook that contains macros, with buttons on the screen that run the macros. One in particular, called clear, clears the contents of the cells. It is not particularly complicated, about 4 lines in all. It is not an absolute reference macro. We will call this workbook Original.xls.

    I wanted to copy the worksheet into another workbook and use the same macro and buttons. I copied the worksheet into a new workbook, saved the workbook (we will call this one Test.xls), and ran the macro by clicking on the button. The macro opened the Original .xls and ran the Clear macro in that workbook, even though that workbook was closed and I clicked on the clear button in Test.xls.

    I looked at the macro list in Original.xls and found that when I highlighted the Clear macro in the list, the macro name box right above it showed the full path of the workbook file and then the macro name, like this: C:StudentOriginal.xls!clear. The path was shown in the small box above the macro list, not in the list itself.

    I opened the Test.xls workbook, looked at the macro list, and found no such path next to the macro name Clear. So I typed in the full path to the current workbook, so that the macro name box would read C:MyDataText.xls!clear. I saved the workbook and reran the macro. It STILL opened the original workbook and ran the macro against the original workbook.

    For clarification purposes, the original workbook and macros were supplied by a consultant, so there is the possibility that the consultant somehow made the macros non-transferrable. Frankly, I'm as interested in how to make them work on the active workbook as I am to find out how you can make macros work against a workbook in an absolute reference sort of way.

    What's going on, and why does my macro do this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Macro (2002/03)

    - You must copy the module or modules containing the macro code from Original.xls to Test.xls. The easiest way to do this is to open both. You can then drag the code module(s) from Original.xls to Test.xls in the Visual Basic Editor.
    - Then do the following for each button:
    - Right click it and select Assign Macro... from the popup menu.
    - In the dialog that appears, select This workbook in the "Macros in" dropdown.
    - Select the appropriate macro.
    - Click OK.

  3. #3
    Star Lounger
    Join Date
    Apr 2003
    Location
    Morgantown, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (2002/03)

    Hans,

    Sorry, forgot to mention that part. I copied the module from Original.xls to Text.xls, saved the text.xls workbook, and closed the Original.xls workbook. I then right-clicked on the button on the worksheet that runs the macro and checked it, The macro name was the full path name to Original.xls and then the macro name, like this: C:StudentOriginal.xls!clear. So I changed it to C:Studenttest.xls!clear and saved the workbook again.

    Then when I clicked on the button, the macro still opened the Original.xls workbook and ran the Clear macro against the Original.xls workbook sheet, even though the Original.xls workbook was not open at the time and there was no apparent reference to any other workbooks in the macro list.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Macro (2002/03)

    Either select the macro from the list, or enter its name without specifying the path and file name.
    Try doing this again, for each button. It really should work.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •