CODEWITHSUNDEEP
excelvba
Jabberwocky
Jabberwocky

Reputation: 50775

How to enable events so Workbook_BeforeSave gets called

My Workbook_BeforeSave event is not called before saving

This is my code:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   a = MsgBox("Do you really want to save the workbook?", vbYesNo)
   If a = vbNo Then Cancel = True
End Sub

This is probably normal, because events are probably not enabled. Now I tried to put Application.Events = True like this:

Option Explicit
Application.Events = True

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   a = MsgBox("Do you really want to save the workbook?", vbYesNo)
   If a = vbNo Then Cancel = True
End Sub

This doesn't change anything, Workbook_BeforeSave is still not called up on saving. But when I close the excel file, following error message is displayed :

enter image description here

The english translation is "Compilation error: Incorrect instruction outside of a procedure."

Apparently the Application.Events = True is not at the right place, but where should I put it ?

Upvotes: 4

Views: 18967

Answers (1)

ZAT
ZAT

Reputation: 1347

Hope these will help:

  1. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) must be inside ThisWorkbook in a VBA project.

  2. Application.EnableEvents = True can not be inserted outside procedure or function.

  3. Events are enabled by default. So, there must be somewhere inside vba project Events are getting disabled. This can be searched by :

    Once you are inside VBA project, Press Ctrl+F to open the Find dialog box. Then search for application.enableevents in the current project. Press Find Next. See the image below.

  4. You can use a little sub to change and view the Application.EnableEvents status (ON/OFF). Place the sub under any standard module. See the image below.

enter image description here

Upvotes: 7

Related Questions