Class Module unhooked after Userform shown

[Shred Dude]

I've put together a simple class to handle Workbook Events. In there is a Worksheet Before Double Click event Handler. It all works well, until I launch a User From from within that event handler.

The code below works endlessly, until I uncomment the part to show a user form. Then it will work the first time, but after that I lose reference to the workbook. Double-clicking the sheet after the first time the form shows, gets you into edit mode. the class is no longer hooked up.

I stepped through the code with a Watch on the cmwkbUI variable. It stays defined until the end of the double-click procedure and then goes to nothing.

The Userform is empty except for one button, with an Unload Me statement attached to it. Is that unloading my class (too) since that's where I called the form from?

What am I missing here?

[VBA]Option Explicit
Private WithEvents cmwkbUI As Excel.Workbook

Private Sub Class_Initialize()

Set cmwkbUI = wkbUI

'If Not cmwkbUI Is Nothing Then
' MsgBox "Class Level Workbook Hook established.", vbInformation + vbOKOnly, "Debugging"
'End If

End Sub

Private Sub cmwkbUI_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

Select Case Sh.CodeName
Case "wksProjRev"
If bUpdating Then Exit Sub

If Not Intersect(Target, Sh.[trash]) Is Nothing Then
MsgBox "Double Click Event fired.", vbInformation + vbOKOnly, "Debugging"

'Causes class to lose hook to workbook...
' Dim frmEdit As New ufEditContract
' frmEdit.Show
'
Cancel = True
End If

Case Else

End Select


End Sub[/VBA]

[Shred Dude]

Wouldn't you know it... soon as I post I get an idea and it works out.

If I show the UserForm as Modless, I keep my reference to the Class. Further use of the Watch window revealed my instance of the Class was disappearing after the Userform was unloaded, but no Class terminate Event was firing.

I don't understand that just yet.

The code below allows the class to continue working after the Userform is unloaded.

Any ideas on how to make this work with a modal form? In my scenario I'd like for the User to have to complete the form that pops up, or Cancel out of it, before being able to proceed with activities on the worksheet.

[VBA]Option Explicit
Private WithEvents cmwkbUI As Excel.Workbook

Private Sub Class_Initialize()

Set cmwkbUI = wkbUI

If Not cmwkbUI Is Nothing Then
MsgBox "Class Level Workbook Hook established.", vbInformation + vbOKOnly, "Debugging"
End If

End Sub

Private Sub Class_Terminate()
MsgBox "Class Terminating!", vbExclamation, "Debugging"
End Sub

Private Sub cmwkbUI_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

Select Case Sh.CodeName
Case "wksProjRev"
If bUpdating Then Exit Sub

If Not Intersect(Target, Sh.[trash]) Is Nothing Then
MsgBox "Double Click Event fired.", vbInformation + vbOKOnly, "Debugging"

'Showing Form Modeless keeps Class intact
Dim frmEdit As New ufEditContract
frmEdit.Show vbModeless
'
Cancel = True
End If

Case Else

End Select


End Sub[/VBA]