Geek asks and answers

How to force SaveAs instead of Save

I want the user to not be able to save the book with the same name with which it was opened, and offer the 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 does not work. The SaveAs dialog box is not displayed.

Next try

If ThisWorkbook.Name = "abc" Then ThisWorkbook.ReadOnly = True
'Error - can't assign to read only property.
+5
source share
3 answers

If you want to test only for a specific file name - say abc.xlsm, the code below will stop Save(but pass SaveAs), and then set the attribute ReadOnlyto False so that Savet will be used again in 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
+5
source

, , , , , / ..

  • . , SaveAs.

  • . , (, Workbooks.Open ReadOnly).

+3

( )

, ,

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

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

, Excel 2003

+3

More articles:


All Articles