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
"JLGWhiz" wrote:
> 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" wrote:
>
> > Sorry - that should be any ideas on deleting code then saving and closing in
> > VBA would be appreciated!
> >
> > "loptap" wrote:
> >
> > > 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!
|
Similar Threads
