Confirmation Box before running a macro

Hi everyone another question.

How do I ask for a confirmation box before running a macro, but only one time. What I mean is some of the macros I use loop and it wouldn't be practical to click on "ok" for each time the macro ran.

So again I just need some code to tack on to the beginning of a couple of my macros that will ask me before they run but only once.

If I need to paste in some sample code I will but this question is pretty generic I think.

I'm not sure what you mean by "some of the macros I use loop"

Do you mean you use them repeatedly within one master routine - else how would a MsgBox be called constantly ?

In general:

Please Login or Register  to view this content.

where strName is String variable containing whatever you want to help you identify what it is you're about to run...

You could use a Static variable to hold a count of times the MsgBox has been invoked and only run when the variable is False, eg:

Please Login or Register  to view this content.

It's hard to give a more precise answer but I hope that helps.

For more info on Static variables see: http://msdn.microsoft.com/en-us/library/z2cty7t8.aspx

I added the portion at the top that you suggested but it doesn't loop after adding the code in.

Any code improvements you want to suggest I will take as well. This code is frank'n stein code pasted together from recording macros, some code from random websites like this, and even a few lines of my own code after I learned a bit. It may not be the best but it works!

Also at the bottom you will see the code I have that tells the macro how many times to run, is there a way to get a text box for me to put in how many times I want it to run then have it run that many times...so if I wanted it to run 1 time I put in on then click continue. Or if I wanted it to run 55 times I put in 55 then click ok.

Here is my code.

Please Login or Register  to view this content.

So the code you listed is what exactly - the header's missing... presumably this is your personal.xls code (rotations) ?

just to be clear... you want the message box to appear only once (when you invoked the routine) OR do you want it to appear each time it moves to a new file ?

you said you inserted the Static Boolean but it didn't loop - not sure I follow what that mean... this is where I refer back to my prior question - I think I may have misinterpreted your requirements.

Just to be clear, the code you posted is Rotations sub routine ?
(ie you're doing recursive call within)

OK, let me work through and see if I can streamline etc... that said given I don't know the layout of the data you're altering certain lines will invariably be left in that could most likely be removed... (ie those commands applying default settings re: formats etc... not normally needed)

Without seeing how you're running this in reality it's hard to give a fool proof piece of code... I wonder though if the below will do what you want ?

Please Login or Register  to view this content.

In the above the code is set such that it will continue to loop until such time as the activeworkbook becomes Personal.xls ... I don't run a Personal.xls so I can't test if this (if hidden) ever shows as Active once all others are closed... I'm hoping it does... someone else may be able to shed some light on this.

I altered your Countif iteration such a temp column of formulae are inserted and all tests are evaluated simultaneously and offending rows removed in one swoop rather than iterating rows and repeatign the process.

I altered the code such that Screen Updating, Events & Calc are set so as to give best possible performance, they are reset to original settings at the end of the routine (or should the routine error) ... ScreenUpdating will reset automatically (akin to Alerts) but it's worth resetting manually as others like Events will not .. ie it's good to be consistent.

Altering PageSetup can often be slow due to printer drivers etc... if this causes you major headaches you might want to consider some alternative approaches (see: http://puremis.net/excel/code/039.shtml)

I was also unsure as to why you were adding sheet protection etc given you weren't saving the file... I commented out those commands.

As I say it's unlikely it resolves all your issues but I hope it might help is some way.