CODEWITHSUNDEEP
excelvba
Hannah
Hannah

Reputation: 169

How to Disable Save and Save As using VBA

I am writing a macro and I need to disable the save function on the workbook that VBA has copied and pasted all the information into. Is this possible?

Upvotes: 12

Views: 96408

Answers (3)

Adam Kruzics
Adam Kruzics

Reputation: 41

One more side note; I had difficulties with the above codes because my organization uses HUNGARIAN version of Excel. So in case you use NON ENGLISH EXCEL you must specify the Controls elements in your Excel's local language ... in my case what worked was:

Application.CommandBars("Worksheet Menu Bar").Controls("Fá&jl").Controls("Menté&s má&ské&nt...").Enabled = False Application.CommandBars("Worksheet Menu Bar").Controls("Fá&jl").Controls("Menté&s").Enabled = False

Upvotes: 0

Francis Dean
Francis Dean

Reputation: 2476

You can use the Workbook_BeforeSave event to achieve this, disabling the CommandBars won't stop your users using a shortcut such as CTRL + S.

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

    MsgBox "You can't save this workbook!"
    Cancel = True

End Sub

Upvotes: 63

Ryan McDonough
Ryan McDonough

Reputation: 10012

You can use the Application object to access the toolbar buttons directly:

Private Sub Workbook_Open() 
    Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save As...").Enabled = False 
    Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save").Enabled = False 
End Sub

Upvotes: 12

Related Questions