+ Reply to Thread
Results 1 to 10 of 10

Button refers to macro in the wrong workbook

  1. #1
    Registered User
    Join Date
    02-11-2013
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    5

    Button refers to macro in the wrong workbook

    Hello,

    First post here... :-)

    My problem is following:

    1. I start with an Excel workbook (let's call it main.xlsm)
    2. I execute a VB macro "Sub main()": this macro does a lot of things, but in the end I have a number of (created) tabs in main.xlsm. Each of these tabs needs to be send (e-mail) to a certain person. Each person will receive one tab (= an excel file with one tab). Tab "projectA" goes to "personA" ... No problem here.
    3. That certain person should open his excel (which only contains 1 tab) and fill in a number of fields.
    4. After having filled in these fields, the person should send the file back to me. To make this structural I want a button on the excel file that - when pressed - automatically sends the file back to me with a fixed subject filled in, to the right mail-address ...

    The files send to the persons do not contain any of the VB code from my macro "Sub main()" (which is good). My macro"Sub main()" automatically creates a button on each excel before mailing it. But behind this button there should be a little piece of VB-code to mail .... the file back to me (step 4). So my macro "Sub main()" does an input of a .bas file into the excel file that is send, let's say it is Sub SendMail(). The button created will execute this macro Sub SendMail().

    Now comes the problem. When my receivers push the button, for some reason it is searching for the macro Sub SendMail() in the original workbook main.xlsm or ... but not in the file itself...

    I do not know if you understand my problem :-)


    Another thing I was thinking was executing a macro before startup when my receivers open the file and this macro will create the button and link it to SendMail() included in the file. This should work, but I don't want to do "macro before startup" when not needed ..

    Help ...

    Thanks

  2. #2
    Registered User
    Join Date
    02-11-2013
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Button refers to macro in the wrong workbook

    Hello,

    To make the answers come more easily following question:

    Is it possible to - when started with an excel file - execute a macro that will generate another excel file with a button on it. This button should execute a peace of code in this newly created excel file?

    Thanks

  3. #3
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Button refers to macro in the wrong workbook

    Ok.

    I would create a sheet specific macro for this.

    So when you mail the individual sheet the macro goes with it.

    I have created a sample and attached it.

    I have made it run when you click on the tab to select sheet 2.

    to view and edit the code, open visual basic, select view project explorer.

    Now right click on sheet two and select view code.

    Enjoy.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    02-11-2013
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Button refers to macro in the wrong workbook

    Hello,

    Thanks for the help, but I am not there yet :-)

    I will explain my question with an example:

    1. I have an excel file with a sheet in it called "Sheet 1" which contains a table with data
    2. I will execute a macro - let's call it "filter-macro" (in personal.xlsb) on this sheet
    3. This macro will filter the data in "Sheet 1" based on a value in column A (let's say column A contains the value "apple" or "cherry")
    4. So first I filter on "apple", copy all the data to a new sheet "Apple". In this sheet my macro creates a button "reply"
    5. Second I filter on "cherry", copy all the data to a new sheet "Cherry". In this sheet my macro creates a button "reply"
    6. My macro has also made a new sheet "main" where there are 2 buttons "apple" and "cherry"
    7. When I press on "apple" the apple-sheet is copied to a new workbook and mailed to a given emailaddress
    8. The same for "cherry"

    What I want is that my senders can open the file, complete it (fill in some fields) and afterwards press "reply". "Reply" should e-mail the file back to a given mailbox (after giving it a name). This is done with the macro "Sub reply", a simple macro that will give the file a name and mail the file.

    Now comes the problem: my macro "filter-macro" creates a new sheet "apple" when pressed on the apple button in sheet "main" and imports a bas-file in this newly created file. This bas-file contains "Sub reply". When the macro "filter-macro" creats the button "reply" it connects this button to the "Sub reply". But for some reason when my receiver opens this file and presses reply it does not execute "Sub reply" but is looking for it in another workbook, I think "Sheet 1" where my original macro "filter-macro is"

    Help ...

  5. #5
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Button refers to macro in the wrong workbook

    Ok

    Try this file.

    It has a macro built in.

    Move the sheet cherry between spreadsheets as you want.

    The button will always work.

    This is because it references a macro actually in the sheet cherry

    the macro is not in the worksheet but in the sheet cherry.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Button refers to macro in the wrong workbook

    In your scenario

    place your sub reply in both cherry and apple as described above.

    ie

    right click on the tab at the bottom of the sheet and select view code.

    Enjoy.

  7. #7
    Registered User
    Join Date
    02-11-2013
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Button refers to macro in the wrong workbook

    Thanks for the help. i think a sheet specific macro should indeed do the job. The sheet where the sheet specific macro should be in (and work) is created by another macro (as mentioned in my previous post). So is there a way to include the macro in the sheet by anothrr macro (right after creating the sheet).

    i know:

    ThisWorkbook.VBProject.VBComponents.Import _
    FileName:="c:/testing.bas"

    But this gives the problem, it is not a sheet specific macro but is placed under Module1 in the workbook ...

  8. #8
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Button refers to macro in the wrong workbook

    why don't you put that in the sheet specific area?

    if it is not programmable then use a mastersheet in your workbook which has that macro attached. Then copy and rename that sheet as required.

    The coding should be easy.

  9. #9
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Button refers to macro in the wrong workbook

    Try This Sample
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    02-11-2013
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Button refers to macro in the wrong workbook

    Thanks mehmetcik

    This works indeed.

    To bad it is not programmable.

    I receive every week/month a workbook with some data in it. On this file I execute my filter-macro and the idea was that I did not have to do any manual interactions.

    In your solution, I should open the workbook, create a mastersheet, add the VB code to this sheet and from that moment on I can execute my macro, copy the mastersheet - rename - past data ... every sheet I send by e-mail will have the sheet-specific code indeed and it will work ...

    Maybe it is possible to - instead of creating new sheets - I create a new workbook for every cherry, apple ... In a workbook I can import a bas-file ... ?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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