Jim Rech wrote:
I don't know the details of your macro, but if it's a "utility macro"
it's best in an add-in. By a utility I mean macro that's handy to
have no matter what workbook is active.

It's task-specific - it takes a plain text Material Requisition generated by
another program and turns it into an Excel workbook (data is split into two
worksheets). Its primary purpose is to speed up processing (which was all
done manually beforehand).


That way the code never
winds up in the saved workbook. It's much easier to never put the
code in, than to strip it out afterward.

I haven't looked into add-ins, but it might be worth a try. Their IT people
seem almost paranoid about security - eg, a laptop user can't change his
mouse settings "because he has a glide-pad, therefore he doesn't need a
mouse"(!). Given that, would doing it as an add-in possibly be a problem in
terms of integrating it?


Even if the code is specific to having a certain workbook open or a
certain worksheet active it might still be a candidate to be in an
add-in. In that case the code would check to see that the
"conditions" are right, and alert the user if there are not.

If you can't go the add-in route you might have your macro make a
copy of the worksheet (ActiveSheet.Copy) and then save that. And
then clear the data from the original. So the user would have two
workbooks open unless you closed one in the code. All depends on
what you want to do...

The original is essentially two blank worksheets, a form data sheet, and an
unpolulated 'template' sheet which is used as the basis for generating the
formatting in the blank worksheets. The toolbar is associated with the
originating sheet - my BeforeSave code can now find the toolbar and delete
it from the new sheet, but leaves it intact in the originating doc.

There can be any number of pages to be generated in the blank worksheets, so
the single page from the template sheet is copied as many times as required,
and then the imported/translated data is added. When the user saves the doc,
I want to keep the two multi-page generated sheets and the form data one.

If I can get the SaveAs dialog up in this code, I can get a new filename
from the user and create the new sheet without the extraneous elements. I
should then be able to finalise the behaviour so that they can start afresh
by making a new sheet via the .xlt. I think... :-)


--
Wally
www.forthsailing.com
www.wally.myby.co.uk