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

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

--
Intellihom
-----------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=147
View this thread: http://www.msusenet.com/t-187057718

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

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


"Intellihome" wrote in message
...

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.

Any ideas would be VERY MUCH APPRECIATED!!!

Ivan


--
Intellihome
------------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=1479
View this thread: http://www.msusenet.com/t-1870577185

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

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

--
Intellihom
-----------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=147
View this thread: http://www.msusenet.com/t-187057718