I have lived with a problem similar to that posted by alexp2000 in May 2004 for
over 12 months. However, none of the suggestions proposed there work for me.
I use a template (itself simply an .xls file, maintaining backwards
compatibility) to produce a fresh monthly account sheet, on demand, for the
month and year selected.
This procedure is coded into the 'Worksheet_SelectionChange' event of the
"ChooseMonth" sheet, which further calls a function 'SaveToMonthlyAccount' in a
Module of the workbook, where several public variables are declared.
This function performs various tasks before deleting the above sheet in the
following code sequence, following which the workbook is saved with its' new
name:
Sheets("ChooseMonth").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
' rem: Sheets("ChooseMonth").Visible = False ' can use this line
instead when debugging, as break points don't work after a .Delete operation
' re-apply protection:
LockTheSheets
' . . . futher code to save the new workbook (ActiveWorkbook.SaveAs . . .)
Contrary to the earlier posters, I believe that no errors occur before the
.Delete line is executed. Break points won't work after this delete, but the function
goes on to save the new workbook as expected. However, global variables, which
I need later in order to properly direct close, save and backup operations, are reset,
as the original poster found.
My reason for this belief is that if I instead disable the .Delete and enable
the line following (setting the sheet's .Visible property to False), then break
points are again obeyed, and global variable values are maintained throughout.
I prefer to 'Delete' the sheet in question, rather than making it invisible, in
order to keep the resulting file size to a minimum. The reason for minimising
this file size is that whenever these monthly accounts are opened, time and
date-stamped backups are saved on each and every of these occasions, building
up to in excess of 500 backups over the course of a year.
Hope someone can help!
over 12 months. However, none of the suggestions proposed there work for me.
I use a template (itself simply an .xls file, maintaining backwards
compatibility) to produce a fresh monthly account sheet, on demand, for the
month and year selected.
This procedure is coded into the 'Worksheet_SelectionChange' event of the
"ChooseMonth" sheet, which further calls a function 'SaveToMonthlyAccount' in a
Module of the workbook, where several public variables are declared.
This function performs various tasks before deleting the above sheet in the
following code sequence, following which the workbook is saved with its' new
name:
Sheets("ChooseMonth").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
' rem: Sheets("ChooseMonth").Visible = False ' can use this line
instead when debugging, as break points don't work after a .Delete operation
' re-apply protection:
LockTheSheets
' . . . futher code to save the new workbook (ActiveWorkbook.SaveAs . . .)
Contrary to the earlier posters, I believe that no errors occur before the
.Delete line is executed. Break points won't work after this delete, but the function
goes on to save the new workbook as expected. However, global variables, which
I need later in order to properly direct close, save and backup operations, are reset,
as the original poster found.
My reason for this belief is that if I instead disable the .Delete and enable
the line following (setting the sheet's .Visible property to False), then break
points are again obeyed, and global variable values are maintained throughout.
I prefer to 'Delete' the sheet in question, rather than making it invisible, in
order to keep the resulting file size to a minimum. The reason for minimising
this file size is that whenever these monthly accounts are opened, time and
date-stamped backups are saved on each and every of these occasions, building
up to in excess of 500 backups over the course of a year.
Hope someone can help!
