vba 关闭excel时VBA密码提示
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23571760/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA password prompt when closing excel
提问by Alessandro
I have a sheet with a Workbook_Open code loading a userform.
After closing the form and the workbook Excel prompts me for the VBA Project password.
I've found some info on this issue on this pages:
http://support.microsoft.com/kb/280454
http://social.msdn.microsoft.com/Forums/office/en-US/8cb79e54-26ae-487c-8945-69b84b2e4eeb/com-addins-and-vba-password-prompt-bug
我有一个带有 Workbook_Open 代码加载用户表单的工作表。关闭表单和工作簿后，Excel 会提示我输入 VBA 项目密码。
我在此页面上找到了有关此问题的一些信息：
http: //support.microsoft.com/kb/280454
http://social.msdn.microsoft.com/Forums/office/en-US/8cb79e54-26ae- 487c-8945-69b84b2e4eeb/com-addins-and-vba-password-prompt-bug
But it seems to be a problems with COM add-ins, which I have some. The problem is that the add-ins are not mine and I can't change or disable them.
但这似乎是 COM 加载项的问题，我有一些。问题是加载项不是我的，我无法更改或禁用它们。
Is there any other solution?
还有其他解决方案吗？
回答by M. Alex
For me, the problem was not some add-ins or unreleased references. It was the Dropbox Badge. As soon as I removed it, the password was not solicited anymore when I closed Excel. To disable Dropbox Badge, open the Dropbox app, go to Settings, then Preferences and in the General Tab select "Never show" under Dropbox Badge. I found this solution on the following forum: https://www.excelforum.com/excel-programming-vba-macros/1100960-ever-annoying-vba-password-prompt-at-close-of-excel-2.html
对我来说，问题不在于某些加载项或未发布的引用。那是 Dropbox 徽章。一旦我删除它，当我关闭Excel时不再请求密码。要禁用 Dropbox 徽章，请打开 Dropbox 应用，依次转到“设置”、“首选项”，然后在“常规”选项卡中选择 Dropbox 徽章下的“从不显示”。我在以下论坛上找到了这个解决方案：https: //www.excelforum.com/excel-programming-vba-macros/1100960-ever-annoying-vba-password-prompt-at-close-of-excel-2.html
回答by smirkingman
This happens when you have hanging pointers to objects. For every 'Set xyz=' you do in VBA, make sure that you have a corresponding 'Set xyz=Nothing'. Similarly for anything pointing to or obtained from COM objects. Make sure you close any ADO connections etc. Be especially careful to handle all errors so that ALL object variables are set to Nothing before the workbook can close, something along these lines:
当您悬挂指向对象的指针时会发生这种情况。对于您在 VBA 中执行的每个“Set xyz=”，请确保您有相应的“Set xyz=Nothing”。对于指向 COM 对象或从 COM 对象获得的任何内容，类似。确保关闭所有 ADO 连接等。要特别小心处理所有错误，以便在工作簿关闭之前将所有对象变量设置为 Nothing，大致如下：
Option Explicit
Option Compare Text
Public Sub Refresh()
On Error GoTo FAIL
Set wb = ThisWorkbook
wb.Activate
' do something useful
DONE:
On Error GoTo 0
Set wb = Nothing
Exit Sub
FAIL:
MsgBox Err.Description
GoTo DONE
End Sub
回答by michaelf
I've had some clients have this issue and I finally started getting it after installing BlueBeam Extreme. I unchecked the BluebeamOfficeAddIn in the COM Add-Ins and the password box stopped popping up when closing my .xlsm file. I'm going to do more digging to see if it's my code but I haven't had this issue until now and disabling that addin seemed to help...
我有一些客户有这个问题，我终于在安装 BlueBeam Extreme 后开始解决这个问题。我取消选中 COM 加载项中的 BluebeamOfficeAddIn，并且在关闭我的 .xlsm 文件时密码框停止弹出。我将做更多的挖掘，看看它是否是我的代码，但直到现在我还没有遇到这个问题并且禁用该插件似乎有帮助......
回答by scott56hannah
I have a number of workbooks that were experiencing the same problem with some users. We went through the process of checking for COM add-ins and various combinations of Windows and Office.
我有一些工作簿，有些用户遇到了同样的问题。我们完成了检查 COM 加载项以及 Windows 和 Office 的各种组合的过程。
In the end we included the following code as part of the workbook_beforeclose event and the problem was resolved for our users.
最后，我们将以下代码作为 workbook_beforeclose 事件的一部分，为我们的用户解决了问题。
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim intResponse as Integer
'If the workbook needs to be saved then ask the user if they want to save the workbook, if not then exit without saving
'Need a global boolean to ensure the request to save the workbook is not shown twice
If Not ThisWorkbook.Saved And Not blnStartedClose Then
blnStartedClose = True
intResponse = MsgBox("Do you want to Save the this Workbook" & vbNewLine & vbNewLine & _
"Select 'Yes' to save the workbook" & vbNewLine & _
"Select 'No' to close without saving", vbYesNo, "Confirm - Workbook Save?")
If intResponse = vbYes Then ThisWorkbook.Save
End If
'If the user has clicked on 'No' to save the workbook then reset the "Saved" property to TRUE so that when we exit this routine no attempt to save the workbook is made
ThisWorkbook.Saved = True
End Sub
