PDA

View Full Version : Application.EnableEvents problem



av8tordude
04-06-2011, 02:31 PM
Old Version = OV
New Version = NV

Both versions have a splash screen userform that is display with the workbooks are opened and closed. With OV currently open, the code below opens NV. After NV is open, the OV is closed and deleted from my computer (which works as expected.) Only problem is when the OV is closed, it momentarily displays the splash screen userform. To prevent the OV splash screen from displaying, I inserted EnableEvents = False before the OV is closed. Unfortunately by inserting EE = False, it prevents any code in the NV from running.

So, how can I prevent the OV splash screen from displaying when the workbook closes, but still be able to run codes in the NV?

Workbooks.Open (frmVersion.txtFile)
With ThisWorkbook
.Saved = True
.ChangeFileAccess xlReadOnly
'Kill .FullName
.Application.EnableEvents = False
.Close 0
End With

Bob Phillips
04-06-2011, 02:50 PM
What exactly do you mean by a splash screen, a userform, special tab?

av8tordude
04-06-2011, 02:53 PM
a splash screen. my logo on a userform. displays for 5 secs when the workbook opens or closes.

av8tordude
04-06-2011, 11:22 PM
ok...I'll it differently...i have 2 workbooks open. How can I close 1 wbk EnableEvents = False, but keep events enabled on the remaining opened wbk?

I tried...


Workbooks.Open (frmVersion.txtFile)
With ThisWorkbook
.Saved = True
.ChangeFileAccess xlReadOnly
'Kill .FullName
.Application.EnableEvents = False
.Close 0
.Application.EnableEvents = True
End With
but, the events do not stay enabled because the wbk1 has already closed.

Bob Phillips
04-07-2011, 03:12 AM
Maybe try delegating it to a timed procedure using Ontime.

GTO
04-07-2011, 03:29 AM
Hi all,

Say, av8tor - by chance are you trying to close ThisWorkbook without ThisWorkbook's BeforeClose event running?

av8tordude
04-07-2011, 03:35 AM
GTO....since both wbks are opened in the same environment, I'm trying to close wbk1, disable events in wbk1, but leave wbk2 open with the events enabled.

GTO
04-07-2011, 03:52 AM
GTO....since both wbks are opened in the same environment, I'm trying to close wbk1, disable events in wbk1, but leave wbk2 open with the events enabled.

Sorry, we are just having the teensiest communication barrier. By environment, I think you mean that both workbooks are open in the same instance of Excel. Okay, then since wkbk1 is closing wkbk1 (that is, closing itself), what events (subs) in wkbk1 are we trying to avoid?

av8tordude
04-07-2011, 03:59 AM
when wb1 closes, it displays a userform w/ my logo. its a splash screen logo that runs when the wbk opens or closes. When I used EE=False, it doesn't run it (which I want to happen), but since wbk2 is in the same instance, wbk2 is affected. It seems beforeclose routine in wbk1 is running, which i don't want

GTO
04-07-2011, 05:07 AM
when wb1 closes, it displays a userform w/ my logo....When I used EE=False, it doesn't run it...

Okay, this is what I was asking about. Presuming your userform is called from BeforeClose and that the sub opening another workbook before closing ThisWorkbook is in a standard module, while I'm sure Bob's suggestion would work, I was thinking that rather than killing events at all, just set a flag.

In the Standard Module:


Option Explicit
Public bolSkip As Boolean

Sub MySub()
MsgBox "Do whatever"
bolSkip = True
ThisWorkbook.Close False
End Sub

In ThisWorkbook:


Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Not bolSkip Then
UserForm1.Show
End If

End Sub

Does that help?

Mark