EnableEvents and DisplayAlerts problems

[Derek Gadd]

Hi,

I'm having trouble getting a couple of things to work properly -
Application.EnableEvents and Application.DisplayAlerts. I assume the
two problems are related but I don't know the cause.

For the EnableEvents:
I have two comboboxes on a spreadsheet. When the first one is changed
by the user, it updates the contents of the second combobox. This
procedure ends by calling a procedure (FilterSub) that is also run
when the second box is updated by the user. This procedure filters
some data and cuts and pastes it to a worksheet. To prevent automatic
updates of the second combobox causing the second procedure to run
twice, I have set Application.EnableEvents = False. However, it still
runs twice and I can't figure out why! It starts running the change
event at the line indicated below:

Sub FillCombo(Category As Collection, MyCombo As MSForms.ComboBox)
Application.EnableEvents = False
Dim Item
With MyCombo
.Clear 'The following procedure runs after this -I don't want
it to!
.AddItem "<All>"
For Each Item In Category
If Item <> "" Then .AddItem Item
Next Item
.ListIndex = 0
End With
End Sub

Application.EnableEvents is still set to false when the following code
runs:

Private Sub ComboBox2_Change()
FilterSub
End Sub

Am I using the EnableEvents property for the wrong thing?


For DisplayAlerts:
Before closing I have this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
End Sub

but it still asks if I want to save. I've also tried
adding/substituting ActiveWorkbook.Saved = True but it still doesn't
work.

Can anybody help? I'm using Office 2000.

TIA,
Derek

 

[Jim Rech]

EnableEvents has no affect on objects placed on a worksheet. It only
affects pure Excel workbook/worksheet/etc., events. Just like it has no
affect on userforms.

You have to set your own flags, e.g.:

Dim NoEvents as Boolean

Sub FillCombo(Category As Collection, MyCombo As MSForms.ComboBox)
NoEvents = True
...other code
NoEvents = False
End Sub

Private Sub ComboBox2_Change()
If NoEvents = False Then
FilterSub
End if
End Sub

 

[Tom Ogilvy]

Application.EnableEvents only pertains to the Events in the Excel object
library. Controls are in the MSforms object library and are not governed by
this setting.

You would have to use a boolean variable that is checked by the code as the
first line to determine whether to run or not. Have the triggering code set
the boolean to true

Public bBlockEvents as Boolean

Sub SomeEventProcedure()
bBlockEvents = True
' code that triggers myprocedure
bBlockEvents = False
End Sub


Sub MyProcedure()
if bBlockEvents then exit sub
' current code
End Sub


ThisWorkbook.Saved = True

should work, but I believe in the original xl2000, there was a bug
associated with this. I don't recall the particulars.

 

[Tom Ogilvy]

The workaround might have been to use the old AutoClose event. Set Saved =
True in that and don't use beforeClose.