Restrict a macro to only run in its workbook

jaybbb

New Member
Joined
Nov 16, 2010
Messages
38
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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?
 
Upvote 0
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
 
Upvote 0
Maybe because you specify ActiveSheet. Have you tried specifying the workbook? (Perhaps ThisWorkbook but I haven't tested it.)
 
Upvote 0
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' :)biggrin:) 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:
Upvote 0
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!
 
Upvote 0
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. :unsure:

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 :LOL:), that it's just no fun anymore.
 
Last edited:
Upvote 0
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,
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top