CODEWITHSUNDEEP
excelvbaactivex
Roland Deschain
Roland Deschain

Reputation: 709

Excel Control events trigger on application close

I've an Excel-workbook with ActiveX controls on worksheets (not on Userform!). When I close the workbook (without closing Excel) everything works well, but when I close Excel entirely it triggers Change events of ActiveX Comboboxes on the worksheets. This creates errors because it seems that Excel has done some cleaning already by removing controls.

How can I bypass or solve this? These events shouldn't trigger in the first place because no values are changed on close.

Upvotes: 3

Views: 4813

Answers (2)

mischab1
mischab1

Reputation: 1601

Without seeing how your ActiveX controls are setup I can't tell you why their change events are firing. However, assuming the workbook's BeforeClose event fires before the ActiveX change events, you can add a global variable to track when the workbook is closing.

In a code module:

Public g_bClosingWorkbook As Boolean

In ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   g_bClosingWorkbook = True
End Sub

Private Sub Workbook_Open()
   g_bClosingWorkbook = False
End Sub

Then only run the change events if not closing the workbook.

Private Sub ComboBox1_Change()
   If Not g_bClosingWorkbook Then
      ' do stuff here
   End If
End Sub

Upvotes: 2

neil860208
neil860208

Reputation: 167

Insert this sub into a module and use it to save and close both the workbook and Excel. Events are disabled first which should prevent the problem.

Sub closeNoEvents()
   Application.EnableEvents = False
    On Error Resume Next
    ThisWorkbook.Save
    Application.Quit
    ThisWorkbook.Close
End Sub

You also might check to see what code is in "ThisWorkbook"; maybe there is something in Workbook_BeforeClose.

Upvotes: 2

Related Questions