CODEWITHSUNDEEP
vbaexcel
m7vm7v
m7vm7v

Reputation: 63

How to disable 'save' option in Excel but 'save as' should be still working

I need to disable the save option in Excel but I still need the save as option to be working... So I know how to disable both of the option by this VBA: 

Private Sub Workbook_BeforeSave(ByVal SaveUI As Boolean, Cancel As Boolean)
    MsgBox "You can't save this workbook!"
    Cancel = True    
End Sub

But How is it possible to disable save but save us to be working still. Thank you for helping me

Upvotes: 1

Views: 6928

Answers (1)

Fadi
Fadi

Reputation: 3322

Try:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 If SaveAsUI = False Then
  MsgBox "You can't save this workbook!"
  Cancel = True
 End If
End Sub

EDIT:

To delete the code from ThisWorkbook Class Module (delete every thing) we can use this code:

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

 If SaveAsUI = False Then
  MsgBox "You can't save this workbook!"
  Cancel = True

 Else
  With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
   .DeleteLines 1, .CountOfLines
  End With

 End If
End Sub

NOTE:

  • This will delete everything in ThisWorkbook Class Module not only Workbook_BeforeSave
  • We must allow Trust access to the VBA project object model (File > Options > click Trust Center, click Trust Center Settings, and then click Macro Settings).

Upvotes: 1

Related Questions