1

Although if I have the following code 

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   MsgBox "Hello"
   Application.DisplayAlerts = False       
   If Not ActiveWorkbook.Saved Then ActiveWorkbook.Save
End Sub

the workbook still ask to be saved why?

The "Hello" is displayed after the excel pop-up for saving the workbook

The expected behaviour is to avoid the "Do you want to save the workbook" pop-up on closing it (see picture below)

enter image description here

UPDATE all lines of code in Workbook_BeforeClose are fired but although the ActiveWorkbook.Save is fired, the pop-up above still show up

UPDATE 2 Finally I figured it out or at least this solution worked for me after reading this post on StackOverflow I then just out of curiosity disabled the "Analysis ToolPack" add-in and bum.. all work fine now.

Any comments?

brillox
  • 363
  • 3
  • 22
  • https://stackoverflow.com/questions/35426907/difference-between-thisworkbook-name-and-activeworkbook-name-in-vba – braX Jun 15 '20 at 22:46
  • You can disable alerts although you are not giving a lot of info. What is the expected behavior? Telling us what is wrong is only half the picture. – urdearboy Jun 15 '20 at 22:47
  • The expected behaviour is to avoid the "Do you want to save the workbook" pop-up on closing it – brillox Jun 15 '20 at 22:50
  • 1
    You should be using `Me` or `ThisWorkbook` in place of `ActiveWorkbook`. `ActiveWorkbook` could be some other workbook entirely. – Tim Williams Jun 15 '20 at 23:59
  • @Tim WIlliams I tried that too, same result – brillox Jun 16 '20 at 06:48
  • The Help for this event says "If the workbook has been changed, this event occurs before the user is asked to save changes." So it's odd that you're seeing the pop-up *before* the Hello... – Tim Williams Jun 16 '20 at 07:01
  • excatly!!! I am getting totally nut. I am on this since yesterday afetroon and is a nonsense – brillox Jun 16 '20 at 07:39
  • the problem is not if I am using Me or ThisWorkBook , the problem is that the save prompt fire up before any line of code is hit in the Before_Close event; seems like thisworkbook is not the workbook of this application :-) – brillox Jun 16 '20 at 07:40
  • check my update 2 for the solution and please do comment on it – brillox Jun 16 '20 at 10:57

1 Answers1

0

Finally I figured it out or at least this solution worked for me after reading this post on StackOverflow I then just out of curiosity disabled the "Analysis ToolPack" add-in and bum.. all work fine now.

brillox
  • 363
  • 3
  • 22