Rawce,

Keep all of your code in a separate add-in that way there is only one
copy of the code that can be accessed by whichever workbook is active
at the time without confusion.

Regards,

Alan

Rawce wrote:
Hello All,

Many thanks if you can help with my particular problem. Will offer as
much of my wisdom as I can in return (most of which has been stolen off
places like here though).

I have a fairly complex Excel file that uses forms, events and
commandbars to help the user navigate its functionality. Forms are
initialised through a custom commandbar, which is itself handled by
worksheet and workbook events (to initialise upon activating/opening,
remove upon deactivating/closing and modify its contents depending upon
which sheet is selected).

Everything works fine until I have two spreadsheets open. As all these
spreadsheets are based on the same template, they all contain the same
macros and forms, etc. and therefore the same names are used. I can
force (through capturing events) the commandbar to run the correct
subroutine from the module specific to the worksheet (by building up
the filename and subroutine name and using Application.Run). When the
subroutine activates the form (e.g. UserForm1.Show), however, debugging
shows that it seems to run the form from the latest file that I opened
instead of the one I initialised things from. This then gives me Run
Time 1004 _Global errors when the form code starts using
Range.Selections. Rather than do some kind of bodge fix for all of my
Range and Sheet selections, I'd prefer to run the correct form from the
file itself.

Is there a way, similar to the above Application.Run, whereby I can
specify the file to run the form from? I've tried the VBComponents to
hopefully define the form's object heirarchy, but I keep getting the
"Programmatic access to visual basic project is not trusted" error and
as there are many potential users of my template I don't want to have
everyone compromise their security settings just to use the file.

Again, thanks for any assistance you can offer. If you want any of my
(poorly written) code to help diagnose, then please shout.

Cheers,

Ross.