Hooking LIstBox in Class Module

[Shred Dude]

I have an Auto-Open routine in and Add-In that initializes a Class. The Class-Initialize Procedure in that class attempts to set two module level withevents variables, one is a Excel.Worksheet the other a MSForms.Listbox.

The routine is failing on the line that attempts to set the ListBox variable, but only the first time through.

If after the initial attempt, I re-run the Auto_Open procedure, it works fine. I then have the listbox hooked and am successfully trapping its events in the Class Module.

Any ideas as to why it fails on the first time through, but not the subsequent times?

Class Module Code:

[VBA]Private WithEvents mwksReporting As Excel.Worksheet
Private WithEvents mlbreports As MSForms.ListBox

Private Const msMODULE As String = "CwksReportingEvents"

Private Sub Class_Initialize()

Const sPROC As String = "Class_Initialize"

On Error GoTo errorHandler

Set mwksReporting = wkbUI.Worksheets(sSheetTabName(wkbUI, "wksReporting"))

'Fails on this line, at inital opening.
'Works if I re-run the Auto_Open procdure which initializes the Class
Set mlbreports = wkbUI.Worksheets(sSheetTabName(wkbUI, "wksReporting")).lbreports

errorExit:
Exit Sub

errorHandler:

MsgBox "Error Encountered in " & msMODULE & " : " & sPROC & vbNewLine & vbNewLine & _
Err.Number & " : " & Err.Description

Err.Clear

Resume Next

End Sub[/VBA]

[Bob Phillips]

What does the AUto-Open code look like?

[Shred Dude]

Thanks for taking the time to have a look. It still has me stumped. I've been working around it while in Dev mode on this project by just manually re-running the call to initalize the class and then everything works fine.

Below is the Auto_Open code, module level declarations and the supproting Sub that is called from within Auto_Open that actually initializes the Classes (InitGlobals). Rerunning InitGlobals resolves the issue. but why it fails to establish the hook to the listbox in the first go around is a mystery to me still.

[vba]Public gclsJobSheetChange As CwsChange
Public gcWksReporting As CwksReportingEvents
Public gcWkbUIEvents As CWkbEvents
Public wkbUI As Excel.Workbook
Public wkbData As Excel.Workbook

Public Sub Auto_Open()
'Set Reference to Data Workbook
On Error Resume Next
Set wkbData = Workbooks(gsDATAWORKBOOKNAME)
On Error GoTo 0
Application.ScreenUpdating = False
Application.StatusBar = "Opening Application. Please Wait..."
If wkbData Is Nothing Then
'Open the Data WB
Workbooks.Open (gsDATAWORKBOOKPATH & gsDATAWORKBOOKNAME)
Set wkbData = ActiveWorkbook
Else
'No need to do anything, it's already open
End If
'Find UI Workbook, open if needed if open activate it
On Error Resume Next
Set wkbUI = Workbooks(gsUIWORKBOOKNAME)
On Error GoTo 0
If wkbUI Is Nothing Then
'Open the WB
Workbooks.Open (gsUIWORKBOOKPATH & gsUIWORKBOOKNAME)
Set wkbUI = ActiveWorkbook
Else
'Activate it
wkbUI.Activate
End If
'Populate the Misc Data needed in the UI Workbook
Dim rngJobs As Range
Set rngJobs = wkbData.Worksheets(sSheetTabName(wkbData, "wksJobs")).Range("jobs")
Application.Index(rngJobs, , 1).Copy _
wkbUI.Worksheets(sSheetTabName(wkbUI, "wksUI_Misc_Data")).Range("coljobstop").Offset(1, 0)
'Initialize Hooks to Class Modules
InitGlobals

End Sub

Public Sub InitGlobals()
'Procedure to make sure Global Variables are initiated
'Establish hook to Job Sheet in UI Workbook in order to use it's events
Set gclsJobSheetChange = New CwsChange
'Establish Hook to Reporting Worksheet
Set gcWksReporting = New CwksReportingEvents
'Establsih Hook to wbkui events
Set gcWkbUIEvents = New CWkbEvents
End Sub

[/vba]