How to run a worksheet specific macro only on one sheet

[LeanAccountant]

This feels like a macro 101 question, so please don't laugh as I'm still relatively new to macros compared to most of you folks...

I'm trying to add some code so that it only runs on the specific sheet or a range of specific sheets - but this list might change in the future) as a worksheet event... When I have been doing this in the past, I have been copying the code to each of the individual sheets but this now seems to be incorrect.

This time, though, I have created the code on one sheet (with a Private Sub Worksheet_Calculate() event) which launches a macro in a separate module - my logic being to have as little code as possible in each of the sheets for copying, pasting, editing purposes, and centralise the main code in the module.
However, I've noticed that this code is actually being run on all the sheets - even those I do not want it to run on. I thought, while developing this code, that it would only run on the one sheet it had been added to (e.g. right click the worksheet name, view code).

I've clearly misunderstood something in my learning, copying code and adapting it from various posts.

Can you help me, please!

Thanks,
Graham

[jewelsharma]

Graham,
Please share your workbook, & briefly describe what does it need to perform. I'm sure you'll get helpful responses from the forum to develop your VBA skills & improve the code.
From the limited info you have currently provided one can understand your basic issue but can offer little help.

[LeanAccountant]

Thanks jewelsharma,

The code I am trying to run on one sheet intitially while testing, then on several specific ones (but not all the sheets) is:

Please Login or Register  to view this content.

I presently have this code stored in one worksheet in my expectation that it would only work on that one sheet at the moment.


The macro it refers to (which is stored in a module) is:

Please Login or Register  to view this content.

I am checking for inconsistent formula "errors" (e.g. where someone has added a value to a formula and the formula is now different to the adjacent ones) and colouring them (instead of conditional formatting, though I do have some conditional formatting on the worksheet too).

At the moment, the macro is being run across all the sheets in the workbook, but I would only expect it to run on the individual sheet at the moment and then on a specified range of sheets in the near future. I may simply have misunderstood where I need to locate the code (i.e. "user error"), rather than it being a problem with the code itself.

Any help would be appreciated!

Thanks,
Graham

[jewelsharma]

Your macro Formula_error_colour() is coded to run on the ActivesSheet & hence it runs on any sheet that is active at the time. Worksheet_Calculate event, though specific to a worksheet is fired every time any cell on that recalculates. If your workbook is set to auto-calculate, this means it will fire every time anything recalculates, which basically means anytime anything changes in the workbook.
As a fix, look to avoid using ActiveSheet, and use the individual workshhet name.

[LeanAccountant]

Thanks for the reply jewelsharma,

I've moved the code all into the same worksheetto try to eliminate that error, so hopefully that works.
(the majority of the sheet is protected, but this seemed to prevent the macro running, so I've included code to unprotect it, allow the macro to run, then protect it again - does this make sense, or do you think the macro should work even on a protected sheet?)

Please Login or Register  to view this content.

I'm still a little confused by

As a fix, look to avoid using ActiveSheet, and use the individual workshhet name.

as this would mean modifying the code in each worksheet specific to that particular worksheet. If that's the only way, then so be it, but I wondered whether there might be an alternative way - e.g. minimal code in the individual worksheet and one macro residing elsewhere (rather than having 60 or so macros to check and maintain - one for each cost centre). If I have a list of the cost centres in column E on "List of cost centres", do you know what code I would need to add to check against this, please?

Thanks,
Graham

[JOHN H. DAVIS]

Maybe something like this?

Please Login or Register  to view this content.

[LeanAccountant]

Thanks John,

I've deleted the previous code from the worksheet it was in, pasted your code into "This Workbook" and specified 3 sheets to start with, but it still seemed to be running on all the sheets, rather than just the specified ones.

Following jewelsharma's earlier comment, and basing it on your code, I've tried changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

. This seems to limit the code to the particular sheet (so thank you both of you), but now the macro stops at

Please Login or Register  to view this content.

(Run-time error 1004: Application-defined or object-defined error).
When I've been testing the code prior to this post, it seemed to stop here if there wasn't actually an inconsistent formula on the sheet, but as I have deliberately entered an alteration to the formula, I'm surprised this is error is occurring.... any thoughts?

Thanks,
Graham

[JOHN H. DAVIS]

Graham, I'm only taking a stab at it. Not familiar with that error check. Maybe:

Please Login or Register  to view this content.