Running macro twice in quick succession

Hi

My workbook (attached) has a macro "NewParticipator()" the execution of which takes about half a second from invocation to conclusion on my Excel 2003 machine (running under Citrix, Windows Server 2003).. The macro is invoked by clicking on the button labelled "P" in the commandbar attached to the workbook.

If I double-click in very rapid succession on the "P" button, the macro executes twice (as desired) and executes correctly.

If I click on the the "P" button, wait until the macro completes, and then click on it again, then again the macro executes twice, with correct results, as desired.

If I click on the "P" button twice in FAIRLY rapid succession, the second click being just before the conclusion of the first execution, maybe about a quarter of a second, or about half way through the first execution, then the macro runs twice (as desired) but the result contains an error.

The error is in the formula contained in Column K of the worksheet "Participators". In the first row (ie the row that is created by running the macro the first time), the formula in the relevant cell in column K appears as
='Provision (0)'!$D$2
and retunrs a value
"Provision (0)"
This is undesired. "Provision (0)" should never appear in the formula or result returned in any cell in column K of the worksheet "Participators", and the correct formulae and values are as returned when running the macro twice either with a delay between them or with a rapid double-click.

Can anyone tell me please
(1) what is causing this undesirable behaviour, and
(2) what should I do to combat it (other than avoiding running the macro with a delay of a quarter of a second between executions)?

Thanks

Thank you very much for taking the trouble to look at my problem.

I am not sure where to enter the code that you suggest. It looks very much like the sort of code that I would attach to a commandbutton such as might be entered onto a worksheet from the Forms toolbar. This macro is being invoked by clicking on a button in a commandbar (at one time I think it used to be called a toolbar) attached to the workbook. At the moment I cannot even see how to identify the name of the particular button on the command bar. I rightclick on the bar, then choose Customize. Then I right-click on the offending button, expecting to see "Properties ..." in the resulting popup menu, but it is not there.

That said, I think I may have found a kludgy workaround. In all of my previous attempts I set calculation to manual for the duration of the macros, then reset to automatic on conclusion. If the workbook needed to recalculate midway through its execution then I would insert an instruction to calculate at that point in the code. By and large that seemed to work OK, and it certainly cut down on the execution time of macros. However in this case I have just tried the effect of setting calculation to automatic throughout, and I have not yet been able to reproduce the original problem having made that change. The main trouble with this solution is that the execution time of the macro goes up by a factor of 4 or more. That is a lesser evil, if I have to choose between those two options. I would rather a third way, though.

I'm sorry, I do not understand this. The macro is run from a button on a commandbar which I *did* create, and then attached to the workbook.

Ah, now I understand what you mean, I think.
At least, until I try and fail I think I understand. May come back here if I run into problems.
Thanks for the help

I have tried putting the PrepareStart() code into the main body of the NewParticipator() routine, but it made no noticeable difference in relation to the problem.

Thanks for the tip, though, I shall note that for the future.