Adding forms to a workbook programatically

[Kevlarhead]

I have a series of spreadsheets used by workers to record what they've done on a particular day. Each one uses a form to validate their entries.

Every so often though, I need to update the form, and as the number of worker's spreadsheets is steadily increasing this gets more tedious and lengthy every time.

I'd like to create a macro that, given a list of filenames and locations will go through the list, open each file, insert a new userform, save and then close.

I can manage most of this but getting Excel to insert a fresh form is causing problems. I found a MS KB article (219905) telling you how to do this from VB, but unsure how to acheive it from VBA. Any thoughts?

 

[erik.van.geit]

Quick clarification; for my purposes a 'non-trivial' environment means 'one where all required files may not be in one location and where unexpected things may happen (e.g. directories or files may be missing or opened by other users).

probably some filesearch-utilities and error handling will be welcome ...

And I had been considering the security implications of trusting macros to write to the VBE. If I was to digitally sign my macros would it then be possible to trust only those items with my sig, rather than trust absolutely everything? (which is obviously asking for trouble)

yes you can do that
I've almost no experience with this
see http://www.mrexcel.com/board2/viewtopic.php?t=114736

Problem with issuing a new template is that the engineer's work data is held on their own spreadsheet. Issuing a new template would mean a new file, which mean copying across the old data, which is the sort of thing I was hoping to avoid, as it's tedious with 20 workbooks (right now), and stupid with 70 (the future). Hence the interest in changing macros centrally.

yes, that's clear, that was not how I meant "template": for me it's just a workbook with the form in it: anyway you will have to test the form somewhere: so you could use that workbook as source to copy the form