Shred Dude
03-11-2011, 11:36 PM
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?
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
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?
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