CODEWITHSUNDEEP
vbaexcel
Gajju
Gajju

Reputation: 443

How to disable save and protected sheet prompt in excel using VBA

I have an excel sheet which is protected, since the sheet is protected I don't want the user to save it, and I don't want the save the sheet prompt to appear when someone closes the workbook. Till now I have been using this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True
    Application.DisplayAlerts = False
End Sub

Using this code, save feature is disabled but the prompt is still appearing

Similar problem:
Since the workbook is protected whenever someone tries to change the cell content it displays an alert, I want to disable that prompt message as well. Can someone help me to fix this

Upvotes: 1

Views: 5573

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

L42 has already answered part of your question.

I want to disable that prompt message as well.

Do this. While protecting the sheet, unckeck the option Select Locked Cells. Now that will take care of the keyboard input while the sheet is locked and protected.

enter image description here

As for mouse inputs i.e the prompt showing up when you double click on the cell, use this :)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
End Sub

Upvotes: 3

Related Questions