Form Object Difficulties.

[Rawce]

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.

 

[Alan]

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]

Add in? Is that similar to Word in that you can apply a template which
pulls in a common module/form/object? Is that accessed through the Add
In Manager? If so, I'll look into it.

Maintaining a single file will be a hell of a lot easier than updating
multiple instances when I issue bug fixes/enhancements!

Many thanks,

Ross.

 

[Alan]

Ross,

I suppose that there are similarities. You'll need to convert a copy of
your spreadsheet to an add-in (set the IsAddin property to true in
ThisWorkbook - I suggest you also strip out the content of the
worksheets because this extra content will simply add weight without
being of use) and save it as with a .xla extension then reference the
add-in within the spreadsheet you want to use it in so that it is
available.

It's not difficult but you may find it a bit tricky when you do it for
the first time ... but then you will be laughing!

Alan

 

[Rawce]

Yes, I've gone down that route. The advantages are threefold:
1. Eliminates the problem when running two different spreadsheets with
the same forms/modules/objects;
2. Halves the size of the spreadsheets (down to 250Kb) so they run
quicker (especially when you've got multiple ones open);
3. Means I only have to maintain one instance of the module/forms
instead of potentially 100 different files.

The only potential problem is that .xlas aren't specific to certain
files (like the .dots in Word can be), so once I've added this to many
people's Excel, there's the possibility of them running the form on
other files not specific to this project. Hopefully I've made it
specific enough so the commandbar only appears on certain named
worksheets, plus I've made private as many macros as I can, but we'll
have to see. I have told everyone that they can remove the .xla once
they've finished their involvement on the project, but I reckon most
would forget.

Anyway, thanks for pointing me down the right route, you've probably
saved me a load of effort in the long run, plus added a bit more
robustness to my work.

Cheers,

Ross.