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

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


"ben" wrote:

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???

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


"ben" wrote:

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

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


"okaizawa" wrote:

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

--
HTH

okaizawa


ben wrote:
I have an App_sheetchange sub that catches changes to all worksheets and
decides which sub to call based on which sheet, it catches the changes and
evaluates all right, only problem is it repeats itself multiple times (43 to
be exact) on one little sheet change.

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


Sub worksheetchange(wks As Worksheet, target As Range)
MsgBox target.AddressLocal
End Sub


for some reason when stepping through after the end sub command for
worksheetchange it will loop right back the command that called it and repeat
that for quite a while then just end inexplicably
any thoughts on why?