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
Expand|Select|Wrap|Line Numbers
- 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
Expand|Select|Wrap|Line Numbers
- 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_image
(Instancing = Private)
Expand|Select|Wrap|Line Numbers
- 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
Expand|Select|Wrap|Line Numbers
- 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
Any idea/solution what's wrong ?
TIA
Note: All test has been performed under Excel2k