Deleting Code and saving

[loptap]

I have an Excel 2000 template comprising 2 worksheets - 1 for data entry and
1 for "Reporting". I have VBA code associated with the data entry sheet for
two command buttons - first button saves workbook "as is" with dynamic name
based on entered data - this allows additional data entry at a later time.
Second button is used when data entry is complete - it copies and paste
special values on the report sheet, deletes the data entry sheet (which
includes the VBA code) and saves the workbook with a new file name and gives
the option to close the workbook. The workbook IS saved using the VBA (I can
check it with explorer) but UNLESS I click on the toolbar "Save", or use
"File/Save as" or save changes when actually closing I have a problem - when
I next open the save file - first I am asked if I want to enable or disable
macros (there aren't any because they were deleted with the data entry sheet)
then, regardless of enabled or disabled macros The workbook opens and
immediately crashes. If I do click on the toolbar "Save", or use "File/Save
as" or save changes before actually closing then everything is fine - buit it
is noticable that the file size drops by 10KB (template is 70KB, the file as
saved after using the second command button is 50KB but on using any of the
UI save functions it drops to 40KB) - so something is being removed here -
but why doesn't the VBA Save/Save As.. also remove the same stuff? Since I'm
deleting the VBA it is difficult to "make" users save before closing -
particular since the program output is suggesting the saving has been done!
When I started down this route I never expected it to work (how can you
expect a program to delete itself then terminate gracefully?) - but it is so
close that it is now annoying - any ideas on saving and closing in VBA would
be appreciated!

 

[loptap]

Sorry - that should be any ideas on deleting code then saving and closing in
VBA would be appreciated!

 

[JLGWhiz]

If you find out how to do that within the same workbook, I would like to know
also.
It seems to me that if you delete the code, the macro would stop. That is
if VBA would even let you delete the code which is currently running. It
should either crash or throw an error saying you can't do that.

Most folks who don't want the code in distributed copies, copy the contents
of the workbook to a new workbook without the code and put that out to the
public, while retaining the original workbook with code intact. So all the
user has to work with is worksheets with data and possibly formulas, but no
code.

 

[loptap]

Thanks for the response - I have now rationalised myself to the fact that I
can't do exactly what I want - so I agree with you to a certain extent.
However it IS possible to delete the VBA code even whilst it is running - I
know 'cos I am doing it! No errors, no crashes! I guess what is happening is
that the sub-routine is loaded into and executed in memory, I delete the
worksheet (in VBA) then exit the sub-routine; the code no longer exists - but
it is now necessary to to "Save" through the user interface to finish the
clean up - so now I am simply changing the final message shown by VBA to
advise the user that they MUST save before closing!
(my overall problem is that I DO want the code in the distributed copies -it
is not really a workboook/spreadsheet but is an application - it is
distributed as an xlt and it creates a document from a data entry page -
since the resulting documents are archived size, is important so I prefer to
have the 40KB document as opposed to the 75KB application)
Thanks again