Code below is on the sheet where I've put ActiveX combo box named cboOthProv,
for which the linked cell is A1. If I put a stop on the very first line,
then step through, at line Range("A1") = "" execution jumps back to
the start, as if events had never been disabled.

Isn't the change to the combo box through its linked cell an event? And if
it is, then why doesn't Application.EnableEvents = False keep it from
triggering the combo box change code to begin again? This is seriously
sending me around the bend. Any clues/advice are most welcome.

Private Sub cboOthProv_Change()

Application.EnableEvents = False
Set rg = ActiveCell
t = Range("A1")
Range("A1") = ""
Select Case t
Case "Hide unselected": HideNoPicks
Case "Show unselected": UnhideNoPicks
Case "Renewal Changes": BlueNoBlue
Case "Hide Accumulators": AccHiding
Case "Show all Accums": AccUnhiding
End Select
rg.Activate
Application.EnableEvents = True


End Sub

I don't know if that property applies to controls. It does apply to sheet,
workbook and application.



"B Lynn B" wrote in message
...
Code below is on the sheet where I've put ActiveX combo box named
cboOthProv,
for which the linked cell is A1. If I put a stop on the very first line,
then step through, at line Range("A1") = "" execution jumps back
to
the start, as if events had never been disabled.

Isn't the change to the combo box through its linked cell an event? And
if
it is, then why doesn't Application.EnableEvents = False keep it from
triggering the combo box change code to begin again? This is seriously
sending me around the bend. Any clues/advice are most welcome.

Private Sub cboOthProv_Change()

Application.EnableEvents = False
Set rg = ActiveCell
t = Range("A1")
Range("A1") = ""
Select Case t
Case "Hide unselected": HideNoPicks
Case "Show unselected": UnhideNoPicks
Case "Renewal Changes": BlueNoBlue
Case "Hide Accumulators": AccHiding
Case "Show all Accums": AccUnhiding
End Select
rg.Activate
Application.EnableEvents = True


End Sub

Maybe this page on Chip's site will help.

http://www.cpearson.com/EXCEL/Events.aspx




"B Lynn B" wrote in message
...
Code below is on the sheet where I've put ActiveX combo box named
cboOthProv,
for which the linked cell is A1. If I put a stop on the very first line,
then step through, at line Range("A1") = "" execution jumps back
to
the start, as if events had never been disabled.

Isn't the change to the combo box through its linked cell an event? And
if
it is, then why doesn't Application.EnableEvents = False keep it from
triggering the combo box change code to begin again? This is seriously
sending me around the bend. Any clues/advice are most welcome.

Private Sub cboOthProv_Change()

Application.EnableEvents = False
Set rg = ActiveCell
t = Range("A1")
Range("A1") = ""
Select Case t
Case "Hide unselected": HideNoPicks
Case "Show unselected": UnhideNoPicks
Case "Renewal Changes": BlueNoBlue
Case "Hide Accumulators": AccHiding
Case "Show all Accums": AccUnhiding
End Select
rg.Activate
Application.EnableEvents = True


End Sub

ComboBox events work even when application level events are disabled.
Workaround, at the top of the module -

Private mbExit As Boolean

In the code, replace the pair of EnableEvents lines with this

' 1st line
If mbExit then Exit sub
On Error Goto errExit
mbExit = True

'code

errExit:
mbExit = False

Regards,
Peter T

"B Lynn B" wrote in message
...
Code below is on the sheet where I've put ActiveX combo box named
cboOthProv,
for which the linked cell is A1. If I put a stop on the very first line,
then step through, at line Range("A1") = "" execution jumps back
to
the start, as if events had never been disabled.

Isn't the change to the combo box through its linked cell an event? And
if
it is, then why doesn't Application.EnableEvents = False keep it from
triggering the combo box change code to begin again? This is seriously
sending me around the bend. Any clues/advice are most welcome.

Private Sub cboOthProv_Change()

Application.EnableEvents = False
Set rg = ActiveCell
t = Range("A1")
Range("A1") = ""
Select Case t
Case "Hide unselected": HideNoPicks
Case "Show unselected": UnhideNoPicks
Case "Renewal Changes": BlueNoBlue
Case "Hide Accumulators": AccHiding
Case "Show all Accums": AccUnhiding
End Select
rg.Activate
Application.EnableEvents = True


End Sub

I still use EnableEvents, like this:

Private Sub cboOthProv_Change()
If Application.EnableEvents Then

Application.EnableEvents = False
Set rg = ActiveCell
t = Range("A1")
Range("A1") = ""
Select Case t
Case "Hide unselected": HideNoPicks
Case "Show unselected": UnhideNoPicks
Case "Renewal Changes": BlueNoBlue
Case "Hide Accumulators": AccHiding
Case "Show all Accums": AccUnhiding
End Select
rg.Activate
Application.EnableEvents = True

End If
End Sub

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



On 4/22/2010 4:39 AM, Peter T wrote:
ComboBox events work even when application level events are disabled.
Workaround, at the top of the module -

Private mbExit As Boolean

In the code, replace the pair of EnableEvents lines with this

' 1st line
If mbExit then Exit sub
On Error Goto errExit
mbExit = True

'code

errExit:
mbExit = False

Regards,
Peter T

"B Lynn wrote in message
...
Code below is on the sheet where I've put ActiveX combo box named
cboOthProv,
for which the linked cell is A1. If I put a stop on the very first line,
then step through, at line Range("A1") = "" execution jumps back
to
the start, as if events had never been disabled.

Isn't the change to the combo box through its linked cell an event? And
if
it is, then why doesn't Application.EnableEvents = False keep it from
triggering the combo box change code to begin again? This is seriously
sending me around the bend. Any clues/advice are most welcome.

Private Sub cboOthProv_Change()

Application.EnableEvents = False
Set rg = ActiveCell
t = Range("A1")
Range("A1") = ""
Select Case t
Case "Hide unselected": HideNoPicks
Case "Show unselected": UnhideNoPicks
Case "Renewal Changes": BlueNoBlue
Case "Hide Accumulators": AccHiding
Case "Show all Accums": AccUnhiding
End Select
rg.Activate
Application.EnableEvents = True


End Sub