Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Want to disable a Checkbox_click event

  Asked By: Carolina    Date: Nov 08    Category: MS Office    Views: 1234
  

Can a CheckBox_Click Event be disabled? I want to change the
CheckBox's state in Code and not have the infinite loop caused by
re-firing from the code-caused change?

There's probably something simple I'm missing, but...
I have several CheckBoxes on a sheet. They work just fine doing
what I want.
There are situations, however, when the action caused by the
CheckBox fails. When it fails, the CheckBox still shows the changed
state. I'd like to change it back.
However, when VBA changes it back (x_Click = Not x_Click), the
Click Event fires again - an infinite loop.
Disabling Events doesn't (App...EnableEvents = False).
Disabling the check box does make it look disabled, but the event
still fires recursively for ever.

Share: 

 

9 Answers Found

 
Answer #1    Answered By: Charlotte Brown     Answered On: Nov 08

I'm not a fan of suppressing events. I'd much rather ignore them. So,
simply ...

sub my event
if not NestedCall then
NestedCall = true
... all the good stuff
NestedCall = false
end if
end sub

NestedCall is a simple  Boolean variable defined at the module level. You
can take advantage of the fact that Excel will initialise it to false  for
you.

 
Answer #2    Answered By: Abbie Hughes     Answered On: Nov 08

I thought of using an "ignore bit", but had to ask the other question first.

Yes. When writing and debugging the code, an error occurring when events  are
disabled
requires manually re-enabling them before anything works again.

While fitting this into my brain, I see that the same technique will work
for inhibiting the
Sheet_Change event  when changing cell values. The routine needing to change  a
cell value
simply sets NestedCall (to true) and changes away... Another variable, but hey,
they're
pretty cheap. (:-)
I can, instead, call my variable EventsOff.... Or EventsOn and get rid of
all the "Nots" and
the somewhat awkward "If negative Then" . Looks like only one var is needed for
the
whole Project.
Cool.

 
Answer #3    Answered By: Myrtle Wilson     Answered On: Nov 08

The negative IF is deliberate. Excel will default your variable to false
("not nested"). This means that you don't need to initialise it
specifically.

I dislike negative IFs from a style point of view. But this is one time
when there is a slight technical benefit.

> Looks like only one var is needed for the
> whole Project.

I use one per module, rather than making them global across the whole
project. However it's unusual to need more than one in any single module.

Variable naming is a very personal thing, of course, but I find "NestedCall"
to be pretty descriptive.

 
Answer #4    Answered By: Cain Smith     Answered On: Nov 08

> The negative IF is deliberate. Excel will default your variable to
false...
> I dislike negative IFs from a style point of view. But this is one
time when there is a slight technical benefit.

I disliked the neg-If, but I realized the benefit of this after
responding yesterday -- and your words sunk-in.
Also, a name, more palatible in the negative-If paradigm, can be
selected such as "EventsDisabled" or simply "Disabled", or even "Off"
if fingers are getting tired (:-).

If Not Disabled Then
Do whatever... Has a nice ring to it.
EndIf


> > Looks like only one var is needed for the whole Project.
> I use one per module, rather than making them global ...
> However it's unusual to need more than one in any single module.

I know professional programmers prefer locals, but this seems like
a case where a Global works nicely.


> Variable naming is a very personal thing...

Of course, but hearing someone else's idea certianly can help.

I am changing cell values in other (external to the _Change)
module, it is no longer a nested (or recursive) call any more.
I'll set the global "Disabled" to True then change  the cell value.
Then, in the _Change event  handler, it'll ignore. One Global
"Disabled" will work  fine for all "ignore" situations. Since I have
many such places where I change either cell values or CheckBoxes, I
then add the If to each _Change Sub.
Though I thought of the concept of adding such a variable, I hadn't
thought the paradign through, but your example immediately made it
crystal clear and allowed me to think through my needs very well.

 
Answer #5    Answered By: Aaeedah Khan     Answered On: Nov 08

EventsDisabled would suggest to me that events  have been disabled, which is
not strictly the case. It's a nested call, which is why I named it what I
did.

However, it is important that the variable name be instantly recognisable
when you come back  to the code  at a later date, so use a variable name that
you're comfortable with.

 
Answer #6    Answered By: Lucio Ferrrari     Answered On: Nov 08

> EventsDisabled would suggest to me that events  have been disabled,
which is not strictly the case.

True. Just plain "Disabled" seems better to imply (or do we
infer) that *that* particular (event) Sub is disabled.


> It's a nested call, which is why I named it what I did.

No problem there. I have the other case. (also remember, I'm the
guy using lots of Globals) (;-)



> ...when you come back  to the code  at a later date, so use a variable
name that you're comfortable with.

Yep. In 6809 assembler, I developed a laser light show
choreography system in the 80's. [kids love it on Halloween] Now,
understand that I comment out-the-wazoo, for maintenance, and work
hard to make the comments descibe the purpose of an instruction, not
the instruction itself. Some years later, I was reading the code and
found one instruction -- and for the life of me, I could not figure
out its reason for being there. It appeared to be a do-nothing in
that particulat routine. - I know I wouldn't have put it in just for
timing because I was counting cycles - trying to pack as much in the
image rotation code for this 900KHz machine. Code maintenance is an
important consideration in source code.



> ...name it "xx", then do a global change  later.

I'm always using this type of out-of-the-box thing. Thanks for
the reminder.


> Off...
Yea. What is off !?

 
Answer #7    Answered By: Amanda Carter     Answered On: Nov 08

Not sure if this checkbox  is on a form. If it is, try hiding the checkbox;
the call goes something like this:

FormName.CheckboxName.visible = false

 
Answer #8    Answered By: Sallie Hill     Answered On: Nov 08

You can disable  events by using
Application.EnableEvents = False
at the beginning of any event. This disables all events, not only the
one for this particular event. Before exiting the event  routine you
need to reset it using
Application.EnableEvents = True

 
Answer #9    Answered By: Abel Fischer     Answered On: Nov 08

I believe in my original post I stated that this doesn't work  all the time.

 
Didn't find what you were looking for? Find more on Want to disable a Checkbox_click event Or get search suggestion and latest updates.




Tagged: