Resetting Excel commandbars on close event

  • Thread starter Thread starter alanperu
  • Start date Start date
A

alanperu

I am automating certain aspects of Excel (and Word) using c#. I am able
to hide various commandbars and controls from the user. My problem is
that I have difficulty resetting the bars when Excel closes. I have
read some articles and threads relating to the Excel close event (or
lack of it) at the application level, but have not found any answers to
this problem.

I am using the workbook_close event to execute some code already, but I
find that when I try to reset the command bars directly from this event
handler Excel just freezes. The only way I have found so far to reset
the bars without any errors is to start a new process from this event
handler that will use a new Excel ApplicationClass and reset the bars,
before closing (without ever being visible). The problem with this is
that I have to insert a delay of around 20 seconds before starting this
new process otherwise Excel bombs out with a memeory referencing
error.

I find it hard to believe that I can remove bars and controls from my
original application, but I cannot reset them from this event handler.

Does anyone know why Excel might be freezing in this way, or how I can
reset the controls without having to use a separate process and a
delay?
 
Hi Alanperu,
I am using the workbook_close event to execute some code already, but I
find that when I try to reset the command bars directly from this event
handler Excel just freezes.
Click to expand...

Try moving the code that handles the commandbars to a sub in a normal
module. Call that sub from the Workbook_BeforeClose event.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
Unfortunately this was something that I have already tried. It seems
that Excel always hangs if the reset code is part of or called from the
event handler. This was why I tried it using a different process.

Thanks anyway,

Any other ideas?
 
Hi Alanperu,
Unfortunately this was something that I have already tried. It seems
that Excel always hangs if the reset code is part of or called from the
event handler.
Click to expand...

Try it like this:

In a normal module:

Option Explicit

Public bClose As Boolean

Sub NowClose()
bClose = True
MsgBox "Place a call to the menuhandler here. "
ThisWorkbook.Close
End Sub

In the Thisworkbook module:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If bClose Then Exit Sub
Cancel = True
Application.OnTime Now, "Nowclose"
End Sub


Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
Back
Top