Application events

[Dave Peterson]

What happened when you skinnied the code down to just its basics?

resaleactive is a public variable in module1 where the worksheetchange sub is
at
the app_sheetchange sub is in the class module
xl2003 sp1 win xp sp2

 

[STEVE BELL]

Ben,

Now that Dave has joined the discussion - you have a source of Excellent
help.
He is far beyond anything I could do to help.

Also - you are using 2003 and I only have 2000.

Stick with Dave...

--
steveB

Remove "AYN" from email to respond

hmmmm i don't know about that, i only have one worksheet in the workbook
that
was being caught. is there a change it would do so if they were group to
another workbook? (as far as i know i ahve no sheets grouped)
Ben

 

[okaizawa]

Hi,

there might be many instances of the class possibly.
it could be checked by the following.
if all addresses are the same, there is one instance.

'your class module

'Add this line at the top of the module
Private cnt As Long

Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range)
If resaleactive = 0 Then Exit Sub
worksheetchange wks, target

'Add this line
cnt = cnt + 1
Debug.Print "variable address:", ObjPtr(Me), cnt

End Sub

 

[Guest]

When i skinned it to just the class code to catch change events and transfer
code to a regular module (with only that one function) in the module it
stopped firing multiple times, i don't have enough experience to know if
maybe i have to class instanced multiple times????
Ben

 

[Guest]

hmm interesting
cnt = 6 and the address changes everytime
how do i go about correcting that? This add-in is actually going to be very
class intensive.
Ben

 

[Dave Peterson]

I think I'd look at the code that was cut out and see if you have anything
making a change.

When i skinned it to just the class code to catch change events and transfer
code to a regular module (with only that one function) in the module it
stopped firing multiple times, i don't have enough experience to know if
maybe i have to class instanced multiple times????
Ben

--
When you lose your mind, you free your life.

What happened when you skinnied the code down to just its basics?

 

[Guest]

i believe i may know what's going wrong but not sure how to correct it. I
have on the worksheet that is to be used, many embedded controls, rather that
right code for all of them onto that worksheet i use my .xla to class all of
them into groups (checkboxes,command buttons, labels, spin buttons, etc...).
they class when the worksheet is loaded, or activated, etc... when i remove
that classing sub then they event triggers only once, i have a feeling my
coding for that is causeing more than one instance of my class to appear,



Sub classbuttons()
Dim clsCBEvents As Class1
Dim lblbuttons As Class1
Dim sbbuttons As Class1
Dim sbuttons As Class1
Dim shp As Shape
Set mcolEvents = New Collection
Set sbuttonevents = New Collection
Set lblevents = New Collection
Set sbevents = New Collection
For Each shp In ActiveSheet.Shapes
If shp.Type = msoOLEControlObject Then
If TypeOf shp.OLEFormat.Object.Object Is MSForms.CheckBox Then
Set clsCBEvents = New Class1
Set clsCBEvents.cbGroup = shp.OLEFormat.Object.Object
mcolEvents.Add clsCBEvents
End If
If TypeOf shp.OLEFormat.Object.Object Is CommandButton Then
Set sbuttons = New Class1
Set sbuttons.comGroup = shp.OLEFormat.Object.Object
sbuttonevents.Add sbuttons
End If
If TypeOf shp.OLEFormat.Object.Object Is MSForms.Label Then
Set lblbuttons = New Class1
Set lblbuttons.lblGroup = shp.OLEFormat.Object.Object
lblevents.Add lblbuttons
End If
If TypeOf shp.OLEFormat.Object.Object Is MSForms.SpinButton Then
Set sbbuttons = New Class1
Set sbbuttons.sbGroup = shp.OLEFormat.Object.Object
sbevents.Add sbbuttons
End If
End If
Next
End Sub



can i change it anyway so that instead of creating a new instance over and
over again it uses the same class that is existing and maybe creating a
seperate class module for the controls would help???

 

[Guest]

ok i have moved all classing of buttons to a new class module and the event
code only triggers once, however now i can not get the button code to fire
right, i have never been able to get two classes working in one project, i
must be missing something.
Ben

 

[Guest]

okaizawa was right i had multiple instances of the class. I have other
replies to him in this thread that explain it.
Ben

--
When you lose your mind, you free your life.

I think I'd look at the code that was cut out and see if you have anything
making a change.

When i skinned it to just the class code to catch change events and transfer
code to a regular module (with only that one function) in the module it
stopped firing multiple times, i don't have enough experience to know if
maybe i have to class instanced multiple times????
Ben

 

[Guest]

okaizawa,

Thank you very much, once i identified that i moved other classing to a
seperate class module and that solved everything!!! thanks much
Ben