CODEWITHSUNDEEP
vbaexcelmacros
AgatonSaxx
AgatonSaxx

Reputation: 67

Prompting a macro on close of workbook, but kill the closing if user press "No"

I am trying to execute a macro on close of a workbook.

The macro works perfectly but the problem is the closing function. I want a user to be prompted to say "Yes" or "No" when closing the workbook. If the user presses "Yes" the workbook should save as xlsm and be closed.

If the user presses "No" the macro should be executed so that the user is sent to sheet "Projektinformation" and the workbook should not be closed.

Here is my code, any thoughts?

Sub Auto_Close()
Dim OutPut As Integer
OutPut = MsgBox("Projektinformation ifylld?", vbYesNo, "Projektinformation.")
If OutPut = 6 Then
'Output = 6(Yes)
ThisWorkbook.SaveAs FileFormat:=xlOpenXMLWorkbookMacroEnabled
Else
'Output = 7(No)
Sheets("Projektinformation").Select
End If
End Sub

Upvotes: 3

Views: 3090

Answers (2)

CodeJockey
CodeJockey

Reputation: 1981

From your comment, I'm inferring that your code looks something like this on the Workbook_BeforeClose side:

Private Sub Workbook_BeforeClose(Cancel as Boolean)
    Call Auto_Close()
End Sub

The problem is the code does exactly what you asked it to! It runs your Auto_Close subroutine (before the workbook closes) and then proceeds to close the workbook!

In order to achieve what you are trying to achieve, you have to change the Cancel parameter, passed into the Workbook_BeforeClose sub, to True. When Cancel = True the workbook will cancel the close event, otherwise it will continue as usual. I would either pass Cancel into your sub by reference and change the flag depending on what your user clicks or make Auto_Close() a function that returns a boolean, indicating whether or not to continue closing the workbook.

EXAMPLE

Private Sub Workbook_BeforeClose(Cancel as Boolean)
    If SomeCondition = True Then
        Cancel = True  '<-- Workbook will stay open
    Else
        Cancel = False  '<-- Workbook will close as usual
    End If
End Sub

Upvotes: 4

user4039065
user4039065

Reputation:

You are putting the code in the wrong place. It should be in the Workbook.BeforeClose Event event macro in the ThisWorkbook code sheet.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim OutPut As Integer
    OutPut = MsgBox("Projektinformation ifylld?", vbYesNo, "Projektinformation.")
        If OutPut = 6 Then
        'Output = 6(Yes)
        ThisWorkbook.SaveAs FileFormat:=xlOpenXMLWorkbookMacroEnabled
    Else
        'Output = 7(No)
        Cancel = True
        Sheets("Projektinformation").Select
    End If
End Sub

Note the Cancel = True. This tells Excel to halt the close operation and continue processing instructions.

Upvotes: 2

Related Questions