Disable Excel save option but allow macro save

Question

I'm creating an excel file and I want to disable the 'save' and 'save as...' option.

I found a lot of solutions on the internet, like this one in VBA:

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

It prevents user from saving changes, but I can't save my changes and that's the problem because I need to do more changes in the VBA code.

Is there a way to save my macro changes ? Like an administrator mode etc... ?

Thank you for your future answers.

AnalystCave.com · Accepted Answer · 2015-06-12 15:31:50Z

1

Use a global variable to override the Save disable:

    Dim override as Boolean

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
         if Not(override) then
           MsgBox "You can't save this workbook!"
           Cancel = True
         end if
    End Sub
    Sub SaveMyChanges()
       override = true
       ActiveWorkbook.Save
       override = false
    End Sub

answered Jun 12, 2015 at 15:31

AnalystCave.com

5,1242 gold badges25 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Awill · Accepted Answer · 2016-10-07 12:05:14Z

0

You can also save while in Design Mode in VBA.

Sorry for the necro, but this works also and is what I do.

answered Oct 7, 2016 at 12:05

Awill

1179 silver badges24 bronze badges

Collectives™ on Stack Overflow

Disable Excel save option but allow macro save

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related