Crash on save

[Steve]

I have been pulling my hair out on this. Can anyone tell me why Excel
2K would crash after the BeforeSave event. My situation is this; I
have a workbook which has three sheet which all contain template style
information. I do not want the user to ever be able to overwrite
these template sheets. Normaly this is not a problem as I have hidden
(read replaced) the menu so they (the user) does not even get the
option. However when I make changes to the source and try to do a
save excel correctly does my BeforeSave code then saves the workbook
then dies. I have included my BeforeSave code below.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim objSheet As Worksheet

Application.DisplayAlerts = False
Application.ScreenUpdating = False

'Make sure we have our place holder sheet
'NOTE: This sheet is required since we can not save
' a workbook with no sheets
On Error Resume Next
Set objSheet = ThisWorkbook.Sheets("Sheet1")
On Error GoTo 0
If objSheet Is Nothing Then ThisWorkbook.Sheets.Add

'Delete all sheets other than place holder sheet
'We save without any of the 'Special' sheets so that
'all that is being saved is the source code for this
'workbook and not the template sheets. When the
'workbook is reloaded the special sheets will be added
'back in (See WorkBook.Open)
For Each objSheet In ThisWorkbook.Sheets
If objSheet.Name <> "Sheet1" Then
g_blnIgnore = True
objSheet.Delete
g_blnIgnore = False
End If
Next

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Any thoughs
Thanks
Steve

 

[Patrick Molloy]

You assume that th eadded sheet is called sheet1. It may
not always be so if the user has a different default
template

replace:
If objSheet Is Nothing Then ThisWorkbook.Sheets.Add
with:
If objSheet Is Nothing Then
set objSheet = ThisWorkbook.Sheets.Add
objSheet.Name="Sheet1"
End If

This forces the sheet name to Sheet1 whatever its default
was. What does the variable g_blnIgnore do?
If you're trapping the workbooks SheetActivate event
(which your loop would trigger) then you can stop this
with :
Application.EnableEvents = False
before your FOR...Next loop


Patrick Molloy
Microsoft Excel MVP

 

[Steve Redmyer]

Thanks for the tip on the added sheet name, I did not even think about
the name because this app has limitted distribution (myself and my
father) and I know the setup of these machines.

As for the g_blnIgnore flag, you are correct in your assumption that it
is for flagging the SheetActivate event to be ignored. I did not
realize there was a method for turning off events. I can not see how
this would cause the saving/crash issue but it does sound like a better
solution to ignoring the event under certain situations and I will
implement it.

Also I noticed in my posting that I mis-identified the version of Excel
as 2000. The problem is actually occurring on my machine which is Excel
2002 and on another machine with Excel XP.

Thanx again for the tips.
Steve