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!