Double up msg box

Hi
I'm trying to run this Sub before closing my workbook. If this is the only workbook open, I want to quit excel, but if there's more workbooks open, I just want to close this one. My problem is that the messagebox appear two times. Does enyone know how to fix this? I tried to put in the enableevents property, but I don't even know if I'm on the right track here...

Sub Auto_Close()





 Dim answer As Integer

 answer = MsgBox("Alle endringer du har gjort vil bli lagret. Der som du ikke ønsker dette, må de omgjøres før du avslutter. Vil du fortsatt avslutte?", vbYesNo + vbQuestion, "VIKTIG")

 If answer = vbYes Then

 Application.ScreenUpdating = False

 Worksheets("Brukerlogg").Activate

 ActiveSheet.Unprotect Password:="password"

 Range("F10").Value = Now()

 ActiveSheet.Protect Password:="password"

 Application.DisplayFullScreen = False

 Application.ScreenUpdating = True

 ThisWorkbook.Save



 Application.EnableEvents = False

 If Worksheets.Count < 1 Then
 ThisWorkbook.Close
 Else
 Application.Quit
 End If
 Application.EnableEvents = True

 Else
 Cancel = True 
End If
 
End Sub

Hi and welcome to the forum. Unfortunately your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

c00ly81,

2 things. Your code has an if statements that checks if there is more than one sheet... should it not be one open workbook?
Second, if you go to the vba editor, and double click on the "ThisWorkbook" on the list, you can paste the code below, and remove Auto_Close macro. By using the beforeclose event of the workbook you'll avoid the double-triggering of the Auto_Close macro.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim answer As Integer

answer = MsgBox("Alle endringer du har gjort vil bli lagret. Der som du ikke ønsker dette, må de omgjøres før du avslutter. Vil du fortsatt avslutte?", vbYesNo + vbQuestion, "VIKTIG")

If answer = vbYes Then

Application.ScreenUpdating = False

Worksheets("Brukerlogg").Activate

ActiveSheet.Unprotect Password:="password"

Range("F10").Value = Now()

ActiveSheet.Protect Password:="password"

Application.DisplayFullScreen = False

Application.ScreenUpdating = True

ThisWorkbook.Save



Application.EnableEvents = False

If Worksheets.Count < 1 Then  'Should this be Workbooks.Count?
ThisWorkbook.Close
Else
Application.Quit
End If
Application.EnableEvents = True

Else
Cancel = True
End If

End Sub

Re: Double up msg box



Can't seem to edit, but besides workbook.count, should it be > 1 instead of <1?

Arkadi: Thank you so much... Should have seen it myself, but this fixed everything! :D

Can't seem to edit, but besides workbook.count, should it be > 1 instead of <1?

Hi,

Is this AutoRun macro called from the Workbook Close event?

It comes up twice because of the first line after the DIM statement, and then presumably again when the workbook is closed later in the macro. Not sure why the enable events isn't preventing that.

But 2 questions.
1. If it's fired by the Workbook close event why do you need the Thosworkbook.close instruction? Why not just let autorun and retrurn control back to the Workbook Close event so that is closes naturally? Which of course raises the next question..

2. Why do you need an autorun macro. Why not just put the relevant code in the Workbook_Close event in the first place?

Try this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Dim answer: answer = MsgBox("Alle endringer du har gjort vil bli lagret. Der som du ikke ønsker dette, må de omgjøres før du avslutter. Vil du fortsatt avslutte?", vbYesNo + vbQuestion, "VIKTIG")
   If answer = vbYes Then
    Application.ScreenUpdating = False
    Worksheets("Brukerlogg").Activate
    ActiveSheet.Unprotect Password:="password"
    Range("F10").Value = Now()
    ActiveSheet.Protect Password:="password"
    Application.DisplayFullScreen = False
    Application.ScreenUpdating = True
    ThisWorkbook.Save
    Application.EnableEvents = False
    If Worksheets.Count < 1 Then ThisWorkbook.Close Else Application.Quit
   Application.EnableEvents = True
  Else
   Cancel = True
  End If
End Sub