problem with open events and opening several workbooks at once

[Brigitte_P]

I have several workbooks (over a hundred) that each have the same procedure running when they open. As it is now, the procedure is duplicated in each workbook because I thought that may make it easier for several users than having to worry about add-ins.

The procedure unprotects a tab named "Current", clears it, then makes a copy of the first tab in the workbook (Sheets(1)) into that tab. It then gets out of cut-copy mode and protects the tab again. Finally, it selects the first cell of the first tab of the workbook because the Current tab is meant for linking, not editing, so it's not a good one to have active each time you open the workbook.

The procedure is working perfectly fine as long as I open only one workbook, but if I open five of them at once, it's hit and miss. Sometimes I have no trouble, sometimes I get an error saying it could not activate the Current tab. When that happens, it can be for one of the workbooks or more. It's pretty random.

The exact same procedure is ran as a Before_Save event. The same kind of phenomenom happens when I save the workbooks: it works perfectly as long as I am only saving one, but it I try to save several at once (by closing Excel and selecting "Yes to All" when it asks whether I want to save the workbooks), I get an error saying it can't activate the Current tab. I have also gotten type mismatch errors with the same process. Once again, it's all pretty random.

Because the procedure works fine for only one workbook and because it sometimes works for 2, 3, 4 or (rarely) 5 workbooks, I don't think my code is the problem, which is why I haven't posted it.

My guess is that the events are somehow interfering with each other when Excel is trying to open or save several things at once, but it's just a guess.

Has anybody ever had this kind of issues? How did you resolve it? Would going the add-in route (something I would much rather avoid) solve the problem? I'm not even sure how to refer to add-in procedures in a workbook other than the add-in workbook itself, so if that's the solution, I'll also need someone to tell me how to do it. I know how to attach add-in procedures to buttons, but not how to call them from a VB module.

Thanks for your help!

Brigitte

[royUK]

To refer to a sheet in the workbook containing the code use

Please Login or Register  to view this content.

In the case of maybe an addin or another workbook acting on a different worrkbook either specify the workbook or use, but the workbook to act on must be the active one

Please Login or Register  to view this content.