How can I develop a macro so that it runs in one xls (not xlsm) worksheet only?

**Bernard46** · 10-21-2012, 06:38 AM

I would like to develop a macro in a test workbook so that I do not risk messing up my live data. I would then like to move that macro to the live worksheet and define a QAT Icon which is only visible in the live worksheet. The final result should be an xls file which I can send to other people / computers so that the Macro works on these other systems. I can do the development with Either Excel 2007 or Excel 2010 but ideally the final result should run in both environmets.

I have got terribly confused trying to do this and have failed miserably. Can anyone give me some clear instructions on how to do this please?

**mrice** · 10-21-2012, 01:42 PM

Sounds like you need a line at the start of the macro to test the name of the active workbook and then exit immediately if it is not the one desired.

**Bernard46** · 10-21-2012, 05:17 PM

Do I assume from what you say Martin that all Macros are public and the only way to stop them being executed in the wrong workbook is to do the test you suggest?
If all macros are public does that mean that anytime I send a workbook to someone else they get any/all macros I may have written?

**jeffreybrown** · 10-21-2012, 05:27 PM

Since Martin doesn't seem to be around...

Originally Posted by Bernard46

Do I assume from what you say Martin that all Macros are public and the only way to stop them being executed in the wrong workbook is to do the test you suggest?

Essentially yes, but with that being said, the user would have to have a way to run the macros.

Originally Posted by Bernard46

If all macros are public does that mean that anytime I send a workbook to someone else they get any/all macros I may have written?

They are only going to see the macros within that workbook, but not any stored in your personal.xlsb.

Before sending the workbook out you could protect the VBE environment, but caution, don't forget this password or there may be trouble.