Event macro firing twice

[Otto Moehrbach]

Excel 2002, WinXP
Tom Ogilvy, Vasant, and Greg Wilson were very helpful with setting up my
code. The objective was to determine which one of many checkboxes was
clicked on and its value. I am having one problem with it and I think it's
due to some code that I added.
The event macro, in a class module, is:
Public WithEvents CheckBoxGroup As MSForms.CheckBox
Private Sub CheckBoxGroup_Click()
Set i = ActiveSheet.Range(CheckBoxGroup.LinkedCell)
MsgBox CheckBoxGroup.Name & " clicked"
Call CopyData2
End Sub

The line above that starts with "Set i = " is mine. I want to set "i" to
the cell that is linked to the checkbox that was clicked on. My final
purpose is to access the cell to the right of that linked cell.
I reduced my code in the CopyData2 macro to just a few lines to demonstrate
my problem. The CopyData2 macro is:
Sub CopyData2()
Set Dest = Sheets("Results").Range("D1")
i.Offset(, 1).Copy
Dest.PasteSpecial xlPasteValues
End Sub

My problem is that the event macro fires twice when the checkbox is clicked
to True once. This of course causes the CopyData2 macro to execute twice as
well.
Where did I go wrong? Thanks for your help. Otto

 

[STEVE BELL]

Your code may be activating events (I am not sure)
but you can prevent that by wrapping your code -

Application.EnableEvents=False
' your code

Application.EnableEvents=True

 

[Bob Phillips]

Create a flag similar to enableevents.

Public WithEvents CheckBoxGroup As MSForms.CheckBox

Private mEnableEvents as Boolean

Private Sub CheckBoxGroup_Click()
If not mEnableEvents Then
mEnableEvents = True
Set i = ActiveSheet.Range(CheckBoxGroup.LinkedCell)
MsgBox CheckBoxGroup.Name & " clicked"
Call CopyData2
mEnableEvents = False
End If
End Sub

 

[Tushar Mehta]

Are both events for the same checkbox? Or for different checkboxes?

How are you instantiating the various objects? Any chance you have the
same checkbox associated with multiple instantiations of the class?

You may also want to check the value of the checkbox before deciding on
what, if any, action to take.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Otto Moehrbach]

Bob
I appreciate your help. This one has me stumped. I did what you said
and it didn't change anything. The event macro still fires twice. Please
help if you have any ideas. Otto

 

[Otto Moehrbach]

Steve, Bob
I did what Steve suggested and it works. But I have no idea why it
should work.
I put the EnableEvents code in the CopyData2 macro. I played around with
placement of the EnableEvents code until I found the offending line. The
code is as shown below. What this tells me is that the Paste command
triggered the Checkbox event macro, and that doesn't make any sense to me.
The Results sheet has no checkboxes at all. Can anybody explain how this
happens? Thanks for your help. Otto
Sub CopyData2()
Set Dest = Sheets("Results").Range("D1")
i.Copy
Application.EnableEvents = False
Dest.PasteSpecial xlPasteValues
Application.EnableEvents = True
End Sub

 

[Otto Moehrbach]

Tushar
Thanks for your response. Yes, both firings are for the same checkbox
(the MsgBox tells me the number of the checkbox that was clicked). Look at
the subsequent messages in this thread and see what I did with Steve's
suggestion. As I say, it works now but I have no idea why it should. Otto

 

[Otto Moehrbach]

Tushar asked if both firings were for the same Checkbox. Yes they are. The
MsgBox tells me the name of the Checkbox that triggered the event macro.
Otto

 

[STEVE BELL]

It works because you have an event macro somewhere in the sorkbook that gets
fired when your code runs.

The events to look for could be:
Change
Selection.Change
Checkbox change
or a host of others...

These are usually in the worksheet module or in ThisWorkbook module.
It might even be in your Class module.

They each have a use designed by you and should be left alone. But they
need to be ignored when your code is running.

The lines I gave you tell the workbook to ignor any of these events and just
keep going.


Some code to consider for wrapping purposes are:
Application.DisplayAlerts =
Application.EnableEvents =
Application.Calculation =
Application.ScreenUpdating =

and others...

 

[Otto Moehrbach]

Steve
Thanks for your help, but I'm afraid that it's not that simple. I have
only one event macro in this entire workbook. That includes all the sheet
modules as well as the workbook module. The one event macro that I have is
in the Class module and it fires when any checkbox in the active sheet is
clicked on. That event macro is firing twice and it is doing it again. I
said that it was fixed but it isn't. Both firings are triggered by the same
checkbox (the MsgBox in that event macro tells me the name of the triggering
Checkbox).
I will try moving the EnableEvent code and see if I can trap the
offending code. Thanks for your help and I would appreciate any tips you
might have.. Otto

 

[STEVE BELL]

Otto,

You are more than welcome. It is always a pleasure to help (where I can).

On a scale of 0 to 10 I am only a 5.
And Class modules are out of my area of expertise.

Sounds like your expertise is more advanced than mine...

But any time you think I might be of help - just shout it out...

My only other advice is to use the EnableEvents line in the event code to
prevent it from firing itself.

I include most of these in most of my code (but make sure you want it turned
off)
and they do help speed up the code...

EnableEvents
ScreenUpdating
DisplayAlerts