+ Reply to Thread
Results 1 to 4 of 4

Using a macro on workbook1 to create a button in wb2 and assigning macro "wb2!macro"

  1. #1
    Forum Contributor
    Join Date
    03-27-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    290

    Using a macro on workbook1 to create a button in wb2 and assigning macro "wb2!macro"

    Hello,

    I have a macro that essentially does the following:
    • Opens a file
      Adds a button to the opened workbook
      changes the "OnAction" property to a macro that is within the open workbook

    however, I am not able to do the last step...

    here is the code I am using:
    Please Login or Register  to view this content.
    where Report is a Workbook variable that was set to the opened file.

    The error I get is:
    "Cannot run the macro '[wbname]!Module1.OpenHyperlink'. The macro may not be available in this workbook or all macros may be disabled"

    Is there a reason for this or something I can do to fix it?
    NOTE: the workbook is correct and the shape is correct (I have checked)

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Using a macro on workbook1 to create a button in wb2 and assigning macro "wb2!macro"

    Hello penfold1992,

    You need to assign the second workbook to an object variable in the first workbook. Activesheet refers to the worksheet that has the focus. In this case, the ActiveSheet is one of the sheets in the first workbook.

    Unless the second workbook has only worksheet, it would be better to use the worksheet's name rather than ActiveSheet.

    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Contributor
    Join Date
    03-27-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    290

    Re: Using a macro on workbook1 to create a button in wb2 and assigning macro "wb2!macro"

    Ok so I changed it to the following:

    Please Login or Register  to view this content.
    But I get the same error.
    All the elements for the macro, exist on the workbook that I have defined "Report". The shape, the macro, the code all exists within the same location. But for some reason I cannot link them together.

  4. #4
    Forum Contributor
    Join Date
    03-27-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    290

    Re: Using a macro on workbook1 to create a button in wb2 and assigning macro "wb2!macro"

    Just a quick note on the fix,

    the fix is essentially that the name must be:

    Please Login or Register  to view this content.
    I missed out the ' marks.
    Problem solved now

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro only shows "create" button. Can't run/edit/delete.
    By nbpat in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-31-2019, 03:44 AM
  2. Adding "Command Button" and "Messages Box" in VBA Macro
    By sanjeevkumarmc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-09-2014, 07:21 AM
  3. How do i create macro to count number of "Y" or "N" in different worksheets
    By tehseen in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-21-2009, 12:34 PM
  4. command button:how to create or "asign" a macro
    By abfabrob in forum Excel General
    Replies: 5
    Last Post: 10-06-2005, 07:54 PM
  5. [SOLVED] Assigning a macro to a "button"
    By Yvon in forum Excel General
    Replies: 8
    Last Post: 02-08-2005, 07:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1