CODEWITHSUNDEEP
excelvba
Alegro
Alegro

Reputation: 7966

How to force SaveAs instead Save

I want to prevent user to save the workbook with the same name as it is opened with, and to offer SaveAs option. 

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If ThisWorkbook.Name = "abc" Then
Cancel = True
SaveAsUI = True
End If

Also tried 

 If ThisWorkbook.Name = "abc" Then SaveAsUI = True

This code doesn't work. SaveAs dialog doesn't appear.

Next try 

If ThisWorkbook.Name = "abc" Then ThisWorkbook.ReadOnly = True
'Error - can't assign to read only property.

Upvotes: 5

Views: 12282

Answers (3)

brettdj
brettdj

Reputation: 55702

If you want to test for a particular filename only - say abc.xlsm then the code below will stop the Save (but pass SaveAs) then set the ReadOnly attribute to False so Save can't be used again on this file in this session

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Not SaveAsUI Then
    If ThisWorkbook.Name = "abc.xlsm" Then
        Cancel = True
        ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
    End If
End If
End Sub

Upvotes: 5

chris neilsen
chris neilsen

Reputation: 53166

The other answer (read only or template) are both good suggestions

However, if you really want to code it, try this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim fName As String
    If ThisWorkbook.Name  "abc.xlsm" Then 
        If Not SaveAsUI Then
            fName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
            If fName = "False" Then
                MsgBox "File NOT saved", vbOKOnly
                Cancel = True
            Else
                Application.EnableEvents = False
                ThisWorkbook.SaveAs Filename:=fName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
                Application.EnableEvents = True
            End If
        End If
    End If
End Sub

Note: coded for Excel 2007/2010 (If ThisWorkbook.Name "abc.xlsm" Then)

You woill need to change if using Excel 2003

Upvotes: 3

Olle Sjögren
Olle Sjögren

Reputation: 5384

I have two suggestions, but in order to know which is best you have to tell us more details about the surrounding code and how you open/create files etc.

  1. Use a template. If you put your code in a template and add a new workbook, it cannot be saved without the SaveAs dialog.

  2. Make the workbook read only at opening. This can be done in a lot of ways, depending on the design of your project (eg Workbooks.Open with ReadOnly parameter).

Upvotes: 3

Related Questions