Results 1 to 9 of 9

Restrict a macro to only run in its workbook

This is a discussion on Restrict a macro to only run in its workbook within the Excel Questions forums, part of the Question Forums category; Hi, I have setup a macro that will run every 1 minutes but when I am in another workbook the ...

  1. #1
    New Member
    Join Date
    Nov 2010
    Posts
    38

    Default Restrict a macro to only run in its workbook

    Hi,
    I have setup a macro that will run every 1 minutes but when I am in another workbook the macro stop and there is an issue because the macro cannot find the sheet defined in the macro.

    Is there a way to specify that the macro run only in its workbook ?

    Thanks,
    Here is my macro :
    Code:
    Sub MAJ()
    
    ' MAJ Macro
        Sheets("Spread").Select
        Application.Run "BLPLinkReset"
        ActiveSheet.ListObjects("Tableau2").Range.AutoFilter Field:=1, Criteria1:= _
            "Alerte"
            Application.OnTime Now + TimeValue("00:01:00"), "MAJ"
    
    End Sub

  2. #2
    Board Regular Ruddles's Avatar
    Join Date
    Aug 2010
    Location
    Glevum Castra, Britannia
    Posts
    5,470

    Default Re: Restrict a macro to only run in its workbook

    Can I check:-

    > You open the workbook with the macro in it

    > The macro runs every minute - that works okay

    > You close the workbook (and maybe open another one)

    > The macro runs again even though the first workkbook is closed

    Is that what's happening?
    R.
    ---
    On two occasions I have been asked, 'Pray, Mr Babbage, if you put into the machine wrong figures, will the right answers come out?'
    I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.

  3. #3
    New Member
    Join Date
    Nov 2010
    Posts
    38

    Default Re: Restrict a macro to only run in its workbook

    No sorry to not being clear enough

    I keep the workbook in background but the macro want to run in the active workbook so when I look at another sheet in another workbook the macro will try to run in the active workbook which is not the good one.

    thanks

  4. #4
    Board Regular Ruddles's Avatar
    Join Date
    Aug 2010
    Location
    Glevum Castra, Britannia
    Posts
    5,470

    Default Re: Restrict a macro to only run in its workbook

    Maybe because you specify ActiveSheet. Have you tried specifying the workbook? (Perhaps ThisWorkbook but I haven't tested it.)
    R.
    ---
    On two occasions I have been asked, 'Pray, Mr Babbage, if you put into the machine wrong figures, will the right answers come out?'
    I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.

  5. #5
    MrExcel MVP HalfAce's Avatar
    Join Date
    Apr 2003
    Location
    Alaska
    Posts
    9,230

    Default Re: Restrict a macro to only run in its workbook

    I keep the workbook in background but the macro want to run in the active workbook so when I look at another sheet in another workbook the macro will try to run in the active workbook which is not the good one.
    The answer to Ruddles' question is (to me anyway) still not clearly answered.
    So, you're not closing the WB containing the code, but simply deactivating the WB and activating the second one - yes?
    Then are you wanting to prevent the code from running while that workbook is not active, or do you still want the code to run, but only work on the WB that contains the code?

    If you still want the code to run while having a different WB active at the time, then Ruddles has 'shown the way' () by specifying the WB & Sheet(s) of interest in your "BLPLinkReset" code.

    If you simply don't want to run the code unless that WB is active then you'll want to do something like stopping the MAJ routine from running with the Workbook_Deactivate event.


    {EDIT:}
    Which would also mean using the Workbook_Activate event to turn it back on...
    Last edited by HalfAce; Nov 26th, 2010 at 01:33 PM.
    Does anyone else find it kinda cruel that they spelled the word lisp with an s?


  6. #6
    Board Regular Ruddles's Avatar
    Join Date
    Aug 2010
    Location
    Glevum Castra, Britannia
    Posts
    5,470

    Default Re: Restrict a macro to only run in its workbook

    Thanks for expanding on the topic - I was having trouble working out what questions I needed to be asking. I have fallen into the trap myself of setting a task to run periodically with .OnTime, then closing the workbook and wondering why on earth it opened itself a few minutes later!
    R.
    ---
    On two occasions I have been asked, 'Pray, Mr Babbage, if you put into the machine wrong figures, will the right answers come out?'
    I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.

  7. #7
    MrExcel MVP HalfAce's Avatar
    Join Date
    Apr 2003
    Location
    Alaska
    Posts
    9,230

    Default Re: Restrict a macro to only run in its workbook

    Yeah, I gotta admit to a love/hate relationship with OnTime.
    It can be really handy but also a real pain in the a. . . butt!

    For whatever reason, I am completely successful at having them work flawlessly, turn off when the WB gets closed (or whatever) and on when it gets opened or whatever... SOMETIMES, but not always.

    I have even gone in and made sure the code was exactly the same from one use to the other, only to have it work on one but not the other. (And this includes some serious troubleshooting / searching through the workbook to see what the problem might be...)

    I don't really use OnTime very much any more. Not that I don't like it, (nor do I try to avoid it), but it's just frustrated me enough times now, leaving me asking "WTF"?!?!?! (quite loudly sometimes ), that it's just no fun anymore.
    Last edited by HalfAce; Nov 26th, 2010 at 03:06 PM.
    Does anyone else find it kinda cruel that they spelled the word lisp with an s?


  8. #8
    New Member
    Join Date
    Nov 2010
    Posts
    38

    Default Re: Restrict a macro to only run in its workbook

    I hope that this time I will be clear enough

    - I open Workbook 1 with the macro in it, it runs perfectly.

    - I open WB 2 and when WB2 is active I have an out of range message which is normal because the macro from WB1 try to run in WB2.

    - I want macro from WB1 to keep working in WB1 no matter the active workbook.

    I will try to use Ruddles answer but it might be what I was looking for.

    Thanks,

  9. #9
    New Member
    Join Date
    Nov 2010
    Posts
    38

    Default Re: Restrict a macro to only run in its workbook

    Actutaly I cannot get it working using ThiWorkbook , I replace ActiveSheet by thisworkbook but I am not sure that this is the good way to do it.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com