TO Bug or Not TO Bug? That is the question.

[Intellihome]

Hello everyone,

I just wanted to draw your attention to how Excel EnableEvents handle
events for ComboBoxes. So far I have not seen any solution on to how t
turn off handling procedures for ComboBoxes.

Here is the code, I put dashes to keep the formatting, because
Code
-------------------
tags do not work.

- Private Sub Worksheet_Deactivate() 'Sub Worksheet_Change(ByVal Target As Range)
------ On Error GoTo Error_handling

---------With Application

-------------.CalculateFull
-------------.Calculation = xlCalculationManual
-------------.ScreenUpdating = False
-------------.EnableEvents = False

-------------With Sheets("Results (Average)")
------------------.ComboBox1.Value = 0
------------------.ComboBox1.ListFillRange = list
-------------End With '<<< after this line change event would be fired twice, because ComboBox was changed twice

---------End With

- Error_handling:


- Application.Calculation = xlCalculationAutomatic
- Application.ScreenUpdating = True
- Application.EnableEvents = True

End Sub

and so on.

The problem bacomes very significant, when you have lets say 10 comboboxes in the workbook and for each you have to make 10 changes, and then may be do it several times in different procedures. So 10x10xN would be 100xN. So if your N is 10 it adds 1000 extra checkings - IF YOU HAVE A GLOBAL VARIABLE THAT FLAGS IT, other wise it adds 1000 of executions of the handling procedures, which you don't even need.

Any ideas would be VERY MUCH APPRECIATED!!!

Iva

 

[Tom Ogilvy]

EnableEvents only applies to events in the Excel type library. ActiveX
controls are in the MSForms type library.

You would need to use a global variable that you use an indicator.

At the top of each Combobox event, you would check it to determine whether
to perform any actions

In a General Module:
Public bBlockEvents as Boolean

then in an event

Private sub Combobox1_Click()
if not bBlockEvents then
' current code
end if
End Sub

in you code, set bBlockEvents to true before performing action that would
cause events you don't want.

An alternative might be to unlink your comboboxes from the cells and handle
loading and reacting strictly with code.

--
Regards,
Tom Ogilvy

Hello everyone,

I just wanted to draw your attention to how Excel EnableEvents handles
events for ComboBoxes. So far I have not seen any solution on to how to
turn off handling procedures for ComboBoxes.

Here is the code, I put dashes to keep the formatting, because
Code:
--------------------
tags do not work.

- Private Sub Worksheet_Deactivate() 'Sub Worksheet_Change(ByVal Target As Range)
------ On Error GoTo Error_handling

---------With Application

-------------.CalculateFull
-------------.Calculation = xlCalculationManual
-------------.ScreenUpdating = False
-------------.EnableEvents = False

-------------With Sheets("Results (Average)")
------------------.ComboBox1.Value = 0
------------------.ComboBox1.ListFillRange = list
-------------End With '<<< after this line change event would be fired

twice, because ComboBox was changed twice

---------End With

- Error_handling:


- Application.Calculation = xlCalculationAutomatic
- Application.ScreenUpdating = True
- Application.EnableEvents = True

End Sub

and so on.

The problem bacomes very significant, when you have lets say 10

comboboxes in the workbook and for each you have to make 10 changes, and
then may be do it several times in different procedures. So 10x10xN would be
100xN. So if your N is 10 it adds 1000 extra checkings - IF YOU HAVE A
GLOBAL VARIABLE THAT FLAGS IT, other wise it adds 1000 of executions of the
handling procedures, which you don't even need.

 

[Intellihome]

Thank you Tom!

Well, if you are saying that there is no other way besides globa
variable than I should probably stop looking for a solution and leav
it as it is now. I just though that I can speed up my program a little
because I have so many elements and they all trigger when I d
something, and then all those procedures are executed and the
everything needs to be checked like you said.

Thanks Tom, I really appreciate your help!!!

Ivan