Microsoft KB Archive/280454

= Password prompt for VBA project appears after Excel quits =

Article ID: 280454

Article Last Modified on 3/29/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q280454



SYMPTOMS
After running a macro that passes a reference for a workbook containing a password-protected VBA project to an ActiveX dynamic-link library (DLL), you are prompted for the VBA project password when Excel quits.



CAUSE
This problem occurs if the ActiveX DLL does not properly release the reference to the workbook that contains the password-protected VBA project.



RESOLUTION
To correct this problem, design classes in your ActiveX DLL so that workbook references are properly released before the classes terminate.

A common scenario where a class Terminate event does not fire is one where you have a circular reference between objects. A circular reference would be created if, for example, a parent object creates a child object and hands that child object a reference to the parent. If the child's reference to the parent is not released, the parent object will not terminate.

The steps in the following section demonstrate how a circular reference between objects might occur and how the password prompt will appear if the objects hold onto a reference for a protected workbook when Excel quits. The resolution, which is to call a method for one of the objects to break the circular references so that the objects can terminate, is also described below.



Steps to Reproduce Behavior
 In Visual Basic, create a new ActiveX DLL project. Change the project name to ExcelTest.  Change the name of Class1 to ParentClass and then add the following code to ParentClass: Option Explicit

Private oChild As ChildClass Private WorkbookRef As Object

Private Sub Class_Initialize Set oChild = New ChildClass Set oChild.Parent = Me End Sub

Private Sub Class_Terminate Set oChild.WorkbookRef = Nothing Set oChild.Parent = Nothing Set oChild = Nothing MsgBox &quot;ParentClass Terminate Event&quot; End Sub

Public Sub Clear Set oChild.Parent = Nothing End Sub

Public Sub SetWorkbook(o As Object) Set WorkbookRef = o   Set oChild.WorkbookRef = o End Sub   Add another class module, name it ChildClass, and then add the following code to ChildClass: Public Parent As ParentClass Public WorkbookRef As Object  Build &quot;ExcelTest.dll&quot;. Create a new workbook in Microsoft Excel. Press ALT+F11 to open the Visual Basic Editor. From the Insert menu, click UserForm to add a new userform to the VBA project.</li>  From the Insert menu, click Module to add a new module to the VBA project. Add the following code to the new module: Public o As Object Sub MyMacro UserForm1.Show Set o = CreateObject(&quot;ExcelTest.ParentClass&quot;) o.SetWorkbook ThisWorkbook 'o.Clear '<=== Remove comment to demonstrate the workaround. Set o = Nothing End Sub </li> From the Tools menu, select VBAProject Properties. On the dialog box that appears, click the Protection tab. Click to select Lock Project for Viewing. Supply a password and click OK.</li> Save the workbook and close it.</li> Now, to reproduce the problem with the VBA project password appearing when Excel quits: <ol style="list-style-type: lower-alpha;"> Open the workbook that you saved in step 9.

In Microsoft Office Excel 2007, if you are prompted by a security warning that indicates that macros have been disabled, click Options. In the Security dialog box, click Enable this content, and then click OK.</li> Perform one of the following actions: <ul> In Excel 2007, click Macros in the Code group on the Developer tab. If the Developer tab does not a[[ear, click the Microsoft Office Button, click Excel Options, click Popular, click to select the Show Developer Tab in the Ribbon check box, and then click OK.</li> In Microsoft Office Excel 2003 or in an earlier version of Excel, click Macro on the Tools menu, and then click Macros.</li></ul> </li> Select MyMacro in the list, and then click Run.</li> Dismiss the userform that the macro displays.</li> Quit Microsoft Excel.</li> You are prompted for the VBA project password when Excel quits.</li></ol> </li></ol>

When you use the steps above to reproduce the problem, note that the Terminate event for the ParentClass class does not fire even after setting its object in the Excel macro to Nothing. (The absence of the MessageBox with the message &quot;ParentClass Terminate Event&quot; indicates that this event did not fire.)

To correct the problem so that the reference to the Excel workbook is properly released, remove the comment from the line designated in the Excel macro, save the macro, and then repeat the test. When the workbook reference is properly released, the object's Terminate event will fire and you will no longer be prompted for the VBA project password when Excel quits.

Additional query words: XL2007

Keywords: kbexpertisebeginner kbprb KB280454

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.