Excel VBA - problem with class example

Murray Brown
Murray Brown used Ask the Experts™
Hi. In the following procedure that I found in an example of using classes I am trying to cause an error so that the class code below auotmatically protects a worksheet that my code has unprotected, but this doesn't happen as Class_Terminate isn't run. What is Class_Terminate and why doesn't this work?

Public Sub DoSomething()

   Dim objWorksheetProtector As WorkSheetProtector
   Set objWorksheetProtector = New WorkSheetProtector
   objWorksheetProtector.Unprotect Application.ActiveWorkbook.ActiveSheet, "password"
   '... manipulate myWorksheet - may raise an error
   Application.ActiveWorkbook.Name = "?"
End Sub

'--- WorksheetProtector class module ---
Private m_objWorksheet As Worksheet
Private m_sPassword As String
Public Sub Unprotect(Worksheet As Worksheet, Password As String)
    ' Nothing to do if we didn't define a password for the worksheet
    If Len(Password) = 0 Then Exit Sub
    ' If the worksheet is already unprotected, nothing to do
    If Not Worksheet.ProtectContents Then Exit Sub
    ' Unprotect the worksheet
    Worksheet.Unprotect Password
    ' Remember the worksheet and password so we can protect again
    Set m_objWorksheet = Worksheet
    m_sPassword = Password
End Sub
Public Sub Protect()
    ' Protects the worksheet with the same password used to unprotect it
    If m_objWorksheet Is Nothing Then Exit Sub
    If Len(m_sPassword) = 0 Then Exit Sub
    ' If the worksheet is already protected, nothing to do
    If m_objWorksheet.ProtectContents Then Exit Sub
    m_objWorksheet.Protect m_sPassword
    Set m_objWorksheet = Nothing
    m_sPassword = "" 
End Sub
Private Sub Class_Terminate()
    ' Reprotect the worksheet when this object goes out of scope
    On Error Resume Next
End Sub
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2011
Using an error is probably resetting the project and terminating all code abruptly, not cleanly, and hence your terminate event isn't fired.
Hi, murbro.

I believe Rory is right, please see http://office.microsoft.com/en-us/excel-help/HV080557139.aspx...
The Terminate event isn't triggered if the instances of the UserForm or class are removed from memory because the application terminated abnormally. For example, if your application invokes the End statement before removing all existing instances of the class or UserForm from memory, the Terminate event isn't triggered for that class or UserForm.
Murray BrownASP.net/VBA/VSTO Developer


Thanks, murbro.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial