Hi,

OK, forget about solving the crashing in Excel. But if you can assist with
the below design criteria that would be fantastic.

Regards,
Scott

"Scott Bass" <sas_l_739 at yahoo dot com dot au wrote in message
...
Hi,

Problem 0:

First of all, I'm not an Excel macro or VB programmer...

Problem 1:

Here is my design criteria:

* A workbook with 1 to n worksheets
* When the user saves the workbook, cycle thru all worksheets, saving them
to a CSV file in the same directory as the workbook
* Ideally, it would be great if I could detect whether the worksheet had
been modified and actually needed saving. I assume this would greatly
improve performance if I only changed one worksheet out of say 20.
* But it's critical that the CSV's be in sync with the worksheets, so
unconditionally saving of all worksheets is acceptable
* Then save the workbook itself in XLS format
* Return the user to the exact worksheet and cell he/she was editing
before the save
* I want the macro to override the normal save functionality (i.e. run if
the user clicks the save icon), as opposed to linking it to a specific
button added to the workbook.

From Googling the net plus some help from a colleague, here are my first
two attempts:

Version 1:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

On Error Resume Next

ThisBook = ThisWorkbook.Name
ThisPath = ThisWorkbook.Path

Application.DisplayAlerts = False
Application.EnableEvents = False

For Each Sheet In ThisWorkbook.Sheets
Sheet.Activate
ActiveSheet.SaveAs Filename:=ThisPath & "\" & ActiveSheet.Name,
FileFormat:=xlCSV _
, CreateBackup:=False
Next

ThisWorkbook.Sheets(1).Activate
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & ThisBook,
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=True

ThisWorkbook.Saved = True

Application.DisplayAlerts = True
Application.EnableEvents = True

Exit Sub

End Sub

Version 2:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

If SaveAsUI Then GoTo ExitHere

ThisBook = ThisWorkbook.Name
ThisPath = ThisWorkbook.Path

Application.DisplayAlerts = False
Application.EnableEvents = False

For Each Sheet In ThisWorkbook.Sheets
Sheet.Activate
ActiveSheet.SaveAs Filename:=ThisPath & "\" & ActiveSheet.Name,
FileFormat:=xlCSV _
, CreateBackup:=False
Next

ThisWorkbook.Sheets(1).Activate
ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & ThisBook,
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False

ThisWorkbook.Saved = True

Application.DisplayAlerts = True
Application.EnableEvents = True


ExitHe
Exit Sub

End Sub

Neither of these totally meet the design criteria stated above, eg.
returning to the worksheet and cell the user was previously editing. Any
assistance meeting the above design criteria appreciated.

Problem 2:
Both of these macros crash Excel on my work machine, but not my home
machine. It also crashes Excel 2007 as tested by our helpdesk on another
machine. The Excel patch level on both my home and work machines is Excel
2003 (11.8211.8202) SP3.

There are of course differences between my work and home machine, but not
too onerous. I'm an administrator on my work machine, but there will be
some security settings that are different.

I know this is a tough question, but are there any known issues that would
cause the above macro to ***crash Excel*** (therefore it's a bug in Excel,
full stop)??? Or known issues that would cause Excel macros to crash in
general? Or, if you can be bothered to run this, does it crash on your
machine as well?

Thanks for any help you can offer.

Regards,
Scott