G
Geoff
Hi
When I was able to regain access to my xla, thanks Jim Cone, I could find no
error and it seems to be ok since.
However there must be a reason and I would like to know what the cause could
be. The problem occurred after a day of testing wbook open events and
towards the stage when it packed up I began to notice that after any VBE
stops had been removed that an opening wbook would sometimes initiate the VBE
but without error messages.
Because stepping through the code produced no errors either I ignored the
'nuisance' particularly as I found that by uninstalling the xla and
reinstalling it stopped this. It may be the point of VBE entry was possibly
the oApp_SheetActivate event.
What this part of the xla is trying to do is prevent another wbook being
opened before the first one has been correctly processed and closed. To do
this the code looks at the path of a newly opened wbook and if it is not the
correct one then the wbook is closed BUT BEFORE it is activated and I wonder
if it is this which is causing the problem after repeated testing.
The event sequence to open Excel and the first wbook is:
1. Workbook_Open
2 Class_Initialize
3 oApp_WorkbookOpen - the xla
4 oApp_WorkbookOpen - the chosen wbook
5 oApp_WorkbookActivate
To open subsequent wbooks steps 4 to 5 are repeated.
To close Excel and 1 wbook:
1. Workbook_BeforeClose
2. oApp_WorkbookBeforeClose - the xla
3. oApp_WorkbookBeforeClose - the wbook
4. oApp_WorkbookDeactivate
To close more wbooks steps 3 and 4 are repeated.
From the code below, if a second wbook is being opened before it should be,
it is closed. And the sequence changes:
1. oApp_WorkbookOpen - the second wbook
2. oApp_WorkbookBeforeClose - the second wbook
3. oApp_WorkbookDeactivate - the second wbook
4. oApp_WorkbookActivate - the first wbook
but then an additional step:
5. oApp_WorkbookDeactivate - the second wbook ?????
I do not know what is happening here.
Could it be that because the code never allows a wbook to reach the stage of
being activated before it is closed be the problem?
Any advice and help appreciated.
Geoff
Code:
'''file and folder paths
Public Const xlaName As String = "FM 3 WIP.xla"
Public Const myPath As String = "C:\Jobs IP"
Private Sub oApp_WorkbookOpen(ByVal wb As Workbook)
'''get paths
userPath = wb.Path
userPath = Left(userPath, 10)
bJobsIPfolder = False
If wb.Name = xlaName Then '''do prep work if this xla is installed
InstallPopUpMenu
ScreenRes
ElseIf userPath = myPath Then
bJobsIPfolder = True ''' for use in other wbook events
'''prevent other wbooks opening if prime wbook is not processed
and closed
If Workbooks.count > 1 Then
If Not ActiveWorkbook.Name = fileToProcessName Then
If bFinishedHF Then
oMsgBox1 _
oStyle:="OK", _
oNumPrompts:=2, _
Prompt1:=fileToProcessName & " is processed but not
closed", _
Prompt2:="Please close that workbook first", _
Title:="WBookMaker"
LoadfrmMsgBox1
wb.Close False
ElseIf bRestoredHF Or bPendingHF Then
oMsgBox1 _
oStyle:="OK", _
oNumPrompts:=2, _
Prompt1:=fileToProcessName & " is pending", _
Prompt2:="Please process that workbook first", _
Title:="WBookMaker"
LoadfrmMsgBox1
wb.Close False
End If
End If
End If
End If
End Sub
When I was able to regain access to my xla, thanks Jim Cone, I could find no
error and it seems to be ok since.
However there must be a reason and I would like to know what the cause could
be. The problem occurred after a day of testing wbook open events and
towards the stage when it packed up I began to notice that after any VBE
stops had been removed that an opening wbook would sometimes initiate the VBE
but without error messages.
Because stepping through the code produced no errors either I ignored the
'nuisance' particularly as I found that by uninstalling the xla and
reinstalling it stopped this. It may be the point of VBE entry was possibly
the oApp_SheetActivate event.
What this part of the xla is trying to do is prevent another wbook being
opened before the first one has been correctly processed and closed. To do
this the code looks at the path of a newly opened wbook and if it is not the
correct one then the wbook is closed BUT BEFORE it is activated and I wonder
if it is this which is causing the problem after repeated testing.
The event sequence to open Excel and the first wbook is:
1. Workbook_Open
2 Class_Initialize
3 oApp_WorkbookOpen - the xla
4 oApp_WorkbookOpen - the chosen wbook
5 oApp_WorkbookActivate
To open subsequent wbooks steps 4 to 5 are repeated.
To close Excel and 1 wbook:
1. Workbook_BeforeClose
2. oApp_WorkbookBeforeClose - the xla
3. oApp_WorkbookBeforeClose - the wbook
4. oApp_WorkbookDeactivate
To close more wbooks steps 3 and 4 are repeated.
From the code below, if a second wbook is being opened before it should be,
it is closed. And the sequence changes:
1. oApp_WorkbookOpen - the second wbook
2. oApp_WorkbookBeforeClose - the second wbook
3. oApp_WorkbookDeactivate - the second wbook
4. oApp_WorkbookActivate - the first wbook
but then an additional step:
5. oApp_WorkbookDeactivate - the second wbook ?????
I do not know what is happening here.
Could it be that because the code never allows a wbook to reach the stage of
being activated before it is closed be the problem?
Any advice and help appreciated.
Geoff
Code:
'''file and folder paths
Public Const xlaName As String = "FM 3 WIP.xla"
Public Const myPath As String = "C:\Jobs IP"
Private Sub oApp_WorkbookOpen(ByVal wb As Workbook)
'''get paths
userPath = wb.Path
userPath = Left(userPath, 10)
bJobsIPfolder = False
If wb.Name = xlaName Then '''do prep work if this xla is installed
InstallPopUpMenu
ScreenRes
ElseIf userPath = myPath Then
bJobsIPfolder = True ''' for use in other wbook events
'''prevent other wbooks opening if prime wbook is not processed
and closed
If Workbooks.count > 1 Then
If Not ActiveWorkbook.Name = fileToProcessName Then
If bFinishedHF Then
oMsgBox1 _
oStyle:="OK", _
oNumPrompts:=2, _
Prompt1:=fileToProcessName & " is processed but not
closed", _
Prompt2:="Please close that workbook first", _
Title:="WBookMaker"
LoadfrmMsgBox1
wb.Close False
ElseIf bRestoredHF Or bPendingHF Then
oMsgBox1 _
oStyle:="OK", _
oNumPrompts:=2, _
Prompt1:=fileToProcessName & " is pending", _
Prompt2:="Please process that workbook first", _
Title:="WBookMaker"
LoadfrmMsgBox1
wb.Close False
End If
End If
End If
End If
End Sub