Shred Dude
12-01-2010, 02:04 PM
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:
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
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:
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