* Ideally, it would be great if I could detect whether the worksheet had
been modified and actually needed saving.
I didn't do anything with this but it can (and should) be done.

* Then save the workbook itself in XLS format
Since you're using the beforesave workbook event, the workbook will be
saved in it's present format after the procedure runs.

* Return the user to the exact worksheet and cell he/she was editing before
the save
The active sheet/cell won't change, so no return is necessary.

Option Explicit

Private Sub Workbook_BeforeSave _
(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error GoTo errHandler

Dim ThisPath As String
Dim Sheet As Worksheet
Dim FileName As String

With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

For Each Sheet In Sheets 'since you're _
in the workbook module, no workbook _
reference is required when referring _
to this workbook

ThisPath = Path 'same here

FileName = ThisPath & "\" & Sheet.Name & ".csv"

Sheet.Copy
With ActiveWorkbook
.SaveAs FileName:=FileName, FileFormat:=xlCSVMSDOS
.Close 'I took the liberty of closing _
the newly created csv files
End With
Next

Cleanup:
With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = False
End With

Exit Sub

errHandler:
MsgBox Err.Source & " " & _
Err.Number & " " & _
Err.Description
GoTo Cleanup
End Sub

Cliff Edwards