Class modules with OLEObject kill own instances

[bettatronic]

The goal : to have class which is able to add some OLE objects on the sheet runtime. Instances of the class must be accessible from any public/private module.

Okay, I've experimented for so long & getting quite tired (:huh: ) of it. So I think it's time to call some help. I hope there must be some solution. I NEED it.

Well actually what I need ? I need to have some class/object which create some OleObjects (images and buttons) runtime. Instances of the class must be accessible from any module as global object.

Object must becreated inside the Private Sub Workbook_Open() event or via Public Sub Auto_Open() in module. Inside the class are some function/procedures for adding CommandButtons on the sheet.

Sounds pretty easy and should be. But the reality is else (strange :-))...

Everytime I call some of the procedure which works with OLE objects (adding/deleting - I mean Add method) all instances of the class are killed. I'd REALLY like to know what's killing them ???

Without OLE procedures all seems to be working fine - the instances of the class stay alive for all the Excel/workbook session. So problem must be with OLEobjects. But I want to be able working with the OLE objects...

Now I'll provide simplified skeleton of the code of my project:

Modules :: Module1

 
Option Explicit
 
Public ole_obj_img As TOle_object_image
 
Public Static Function New_TOle_object_image() As TOle_object_image
    Set New_TOle_object_image = New TOle_object_image
End Function
 
Public Sub Auto_Open()
 
    Set ole_obj_img = New_TOle_object_image
 
    ole_obj_img.sheet_idx = 1
 
    ole_obj_img.add_image                   'calling this cause destruction of the object (why ?)
 
    'ole_obj_img.inc_counter offset:=10  'this work fine; no OLE inside
 
End Sub
 
 

Modules :: Module2

 
Public Sub alpha()
 
    ole_obj_img.sheet_idx = 2
    'other code
 
End Sub
 
Private Sub betta()
 
    ole_obj_img.sheet_idx = 1
    'other code
 
End Sub
 
 

Class Modules :: TOle_object_ima ge
(Instancing = Private)

 
Option Explicit
 
Private sh_idx As Integer
Dim count As Integer
Dim count_removed As Integer
Private w As Worksheet
 
Private Sub Class_Initialize()
    'constructor
    sh_idx = 0
    count = 0
    count_removed = 0
    MsgBox "TOle_object_image initialized"  'only for debug
End Sub
 
Private Sub Class_Terminate()
    'destructor
    MsgBox "TOle_object_image terminated"  'only for debug
End Sub
 
Property Let sheet_idx(uIdx As Integer)
    sh_idx = uIdx
End Property
 
Property Get sheet_idx() As Integer
    sheet_idx = sh_idx
End Property
 
Public Static Sub inc_counter(ByVal offset As Integer)
    count = count + offset
End Sub
 
Public Static Sub add_image()
    Dim tmp_ole As OLEObject
    Dim i As Integer
    Dim wo As OLEObjects
 
    i = 0           
 
    Set wo = ThisWorkbook.Worksheets(sh_idx).OLEObjects
 
        wo.Add ClassType:="Forms.Image.1", _
            DisplayAsIcon:=False, Left:=10 + 30 * i, Top:=10 + 30 * i, Width:=20, Height:=20
 
    Set wo = Nothing
    count = count + 1
End Sub
 
 

Workbook code:

 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
       Set ole_obj_img = Nothing
End Sub
 
 
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
 
    Application.EnableEvents = False
    Application.ScreenUpdating = False
 
    If Sh.Index <> 1 And Sh.Name <> "system" And Sh.Name <> "runtime" Then
 
        ole_obj_img.inc_counter offset:=255
 
        'other code
 
    End If
 
    Application.ScreenUpdating = True
    Application.EnableEvents = True
 
End Sub
 
 

What's killing the instances after inicialization ? If you know the solution provide some fix-code please too.

Any idea/solution what's wrong ?

TIA

Note: All test has been performed under Excel2k