Declaring something higher than public to work in another workbook?

I have a workbook which contains my macros. This workbook works on other workbooks by loading them and doing things to them.

These other workbooks are used independently from this first macro book, but they have macros built in too that are housed in ThisWorkbook, prompting the user with a MSGbox when they click "Save."

Please Login or Register  to view this content.

This works perfectly, however - There are two ways this can pop up:

1. When I am using the MAIN macro book, utilizing this second book, that second book can be saved - and when it is saved, it automatically goes into ThisWorkbook and prompts whether or not I want to run these cleanup functions.
2. I am actually using the second workbook, I click save, and get the prompt.

I run the MAIN macro book each day, on a set of 12 sheets, so 12 times, my process is interrupted by a prompt where I just click "no." I want to bypass this, so the prompt only happens when individual users are in their respective books.

I tried using a public boolean that gets set to "True" prior to entering the secondary workbook, but this didn't work as none of the variables I set carry over into the new workbook. Is there anything higher than Public that I can set in 1 workbook that will carry over to another workbook when it enters the ThisWorkbook section to run this save prompt?

I hope this makes sense.......

Hi lordterrin,

Here are a couple of approaches I have successfully taken on similar problems:
a. If the name of the MASTER workbook is the same all the time test for ThisWorkbook.Name being the same as the MASTER name which is the same all the time.
b. If a certain cell or cells (or sheet name) in the MASTER always has a value that is not in the other workbooks you can test for that.
c. You can add a hidden cell to the MASTER workbook that is in a specific cell and has a known value (e.g. 'MASTER').

I hope this helps.

Lewis

This is a really good idea - I can make that work!

Thanks!