在工作簿关闭时提示宏，但如果用户按“否”则终止关闭

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.

宏工作完美,但问题是关闭功能。我希望在关闭工作簿时提示用户说“是”或“否”。如果用户按“是”,则工作簿应保存为xlsm并关闭。

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.

如果用户按下“否”,则应执行宏,以便将用户发送到工作表“Projektinformation”,并且不应关闭工作簿。

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

2 个解决方案

#1

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

您将代码放在错误的位置。它应该在ThisWorkbook代码表中的Workbook.BeforeClose Event事件宏中。

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.

注意Cancel = True。这告诉Excel停止关闭操作并继续处理指令。

#2

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

从您的评论中,我推断您的代码在Workbook_BeforeClose端看起来像这样:

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!

问题是代码完全符合您的要求!它运行您的Auto_Close子例程(在工作簿关闭之前),然后继续关闭工作簿!

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.

为了实现您要实现的目标,您必须将传递给Workbook_BeforeClose子的Cancel参数更改为True。当Cancel = True时,工作簿将取消关闭事件,否则它将照常继续。我会通过引用将Cancel传递给您的sub并根据用户单击的内容更改标志,或者使Auto_Close()返回一个布尔值的函数,指示是否继续关闭工作簿。

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

#1