Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

Password prompt for VBA project appears after Excel quits


View products that this article applies to.

This article was previously published under Q280454

↑ Back to the top


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.

↑ Back to the top


Cause

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

↑ Back to the top


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.

↑ Back to the top


More information

Steps to Reproduce Behavior

  1. In Visual Basic, create a new ActiveX DLL project. Change the project name to ExcelTest.
  2. 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 "ParentClass Terminate Event"
    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
    					
  3. Add another class module, name it ChildClass, and then add the following code to ChildClass:
    Public Parent As ParentClass
    Public WorkbookRef As Object
    					
  4. Build "ExcelTest.dll".
  5. Create a new workbook in Microsoft Excel. Press ALT+F11 to open the Visual Basic Editor.
  6. From the Insert menu, click UserForm to add a new userform to the VBA project.
  7. 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("ExcelTest.ParentClass")
        o.SetWorkbook ThisWorkbook
        'o.Clear  '<=== Remove comment to demonstrate the workaround.
        Set o = Nothing
    End Sub
    					
  8. 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.
  9. Save the workbook and close it.
  10. Now, to reproduce the problem with the VBA project password appearing when Excel quits:
    1. 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.
    2. Perform one of the following actions:
      • 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.
      • In Microsoft Office Excel 2003 or in an earlier version of Excel, click Macro on the Tools menu, and then click Macros.
    3. Select MyMacro in the list, and then click Run.
    4. Dismiss the userform that the macro displays.
    5. Quit Microsoft Excel.
    6. You are prompted for the VBA project password when Excel quits.
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 "ParentClass Terminate Event" 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.

↑ Back to the top


Keywords: KB280454, kbprb, kbexpertisebeginner

↑ Back to the top

Article Info
Article ID : 280454
Revision : 6
Created on : 3/29/2007
Published on : 3/29/2007
Exists online : False
Views : 358