Stop Userform Events

[Ken Puls]

Hey folks,

I'm loading up a userform and in the Userform_Initialize event, I am setting all the defaults for the checkboxes. Unfortunately, they all have change events associated with them.

I cannot get the change events to stop firing as I load the defaults. Application.enableevents = false doesn't work for me. Is there another object /event that I'm missing here?

I can code round it be setting a temporary Boolean such as UpdateCB's = True/False, but this seems pretty hokey...

Any ideas?

[BlueCactus]

I feel your pain. I'll be interested if someone has a clean solution to this.

I've been using a counter for this purpose. All my _Change() code comes enclosed in a block like:

If canUpdate = 0 Then
...
...
End If

Everytime I enter code where I want to disable _Change() code, I increment canUpdate, and then I decrement it on the way out. Hokey? Very, but at least it works.

[Ken Puls]

Hmmm... I was afraid of that. Hopefully someone will have a better way, but in the mean time, I'll give this a go.

Thanks!

[chitosunday]

Try the click event. It's much tamer than the change event

[Bob Phillips]

Ken,

This is a common problem, and I use a variable associated with the event to control it, something like

Private mEnableEvents As Boolean

Private Sub UserForm_Initialize()
    With Me
        .CheckBox1.Value = True
        .CheckBox2.Value = False
        'etc.
    End With
mEnableEvents = True
End Sub

Private Sub CheckBox1_Click()
    If mEnableEvents Then
        mEnableEvents = False
MsgBox "1 Changed"
mEnableEvents = True
    End If
End Sub

Private Sub CheckBox2_Click()
    If mEnableEvents Then
        mEnableEvents = False
MsgBox "2 Changed"
mEnableEvents = True
    End If
End Sub

Probably not what you want, but unavoidable I feel.

.

[mdmackillop]

Hi Ken,
Can you not set the default value True/False in the Checkbox Value property, then there is no change occurring.
Regards
Malcolm

[Killian]

I also use a boolean and wrap all the form events in If... Then's
I believe it's the best way to handle it

[Ken Puls]

Hey all,

Thanks for the input. As I said, I was afraid that would be the way, but was hoping that I'd just missed something. It does seem odd though that an Application level command like EnableEvents wouldn't extend to Userforms.

No biggie, and I can code that in.

Chitosunday, I misspoke, they are actually Click events. A click is a good as a change with textboxes though, and my brain was wearing down by the time I posted.

Malcolm, not in this case. I have a (another) userform which captures/changes specific user preferences, and writes them to the registry using getsetting/savesetting. I retrieve those in the Userform startup to set the checkboxes correctly.

Thanks again to all of you. I'm going to mark this one solved.

[Bob Phillips]

It does seem odd though that an Application level command like EnableEvents wouldn't extend to Userforms.

Not so odd, a Userform is not part of the Excel object, it is part of MSForms.

[BlueCactus]

FWIW, I switched to the counter method because the boolean switch was getting too complicated to maintain on some of my larger forms. For example a piece of code that might trigger an event might also call another piece of code that might also trigger an event. That second piece of code might have more than one entry route, and so keeping track of the desired boolean status was just too complex.

The counter is more a fire-and-forget solution. No matter how many iterations and nested Calls get involved, each Sub will stack the counter up, each End Sub takes it down. When it gets back to zero again, events are re-enabled.

[Ken Puls]

Not so odd, a Userform is not part of the Excel object, it is part of MSForms.

Okay, fair enough. In that case I think I'll say it's strange that there is not MSFroms.EnableEvents = False command available!

The counter is more a fire-and-forget solution. No matter how many iterations and nested Calls get involved, each Sub will stack the counter up, each End Sub takes it down. When it gets back to zero again, events are re-enabled.

Interesting! Mine is pretty simple, I just need to turn them off while the Userform_Initialize event takes place, so a boolean would work. I can certainly see how your method would be superior in a more complex setting though. Nice strategy!