Code for "SAVE-AS" works good but not perfect

**mbdigital** · 12-03-2014, 09:33 AM

Hello all.

I hope that somebody can help me with this :

I have a code (received through this forum) in my template and it looks like this :


'Action makes sure the user saves as XLSM file type.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Dim FileNameVal As String

    FileNameVal = Sheets("Gegevens").Range("E31").Value '& ".xlsm"

    Application.DisplayAlerts = False

    ThisWorkbook.SaveAs Filename:=FileNameVal, FileFormat:=52
    
    Application.DisplayAlerts = True
    
End Sub

When the user clicks on the built in excel method "save-as", the code gets the generated filename from a cell (cell E31 on tab "Gegevens")
and makes sure the template is being saved as XLSM.
This works very good.

Little problem is that when the user doesn't choose "save-as" but "save" (or the little disk-icoon in excel to save) the whole sheet freezes and Excel stops working.
Also when the user clicks the "X" (right-upper corner) to end the sheet (and normally asks to save the sheet) it goes wrong.
Only way to work further is close Excel the hard way and hope that the just filled in details (and that can be a lot of details ...) can be restored again.

My question :
Is there a way to make upper mentioned code working perfect by building something in so the "save" function in Excell can not be used and only the "save-as" function is possible ?

In advance .... many thanks for your help.

**mbdigital** · 12-15-2014, 05:10 PM

Can anybody please please please help me in this ?

**shg** · 12-15-2014, 05:36 PM

Maybe ...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Cancel = True
  On Error GoTo Oops

  With Application
    .EnableEvents = False
    .DisplayAlerts = False
    Me.SaveAs Filename:=Worksheets("Gegevens").Range("E31").Value, _
              FileFormat:=xlOpenXMLWorkbookMacroEnabled
    MsgBox "Saved as " & Me.FullName
Oops:
    .DisplayAlerts = True
    .EnableEvents = True
  End With
End Sub