What is the difference between these objects? (SOLVED)

[phantom1975]

I am having difficulty writing object-based code for a worksheet because I don't understand the difference between these references:

OLEObject
Object
Shape
Control

They all seem to be the same thing, but there is obviously some differences. I have tried playing around with different code to see what the differences are, but I keep getting errors. What are the differences between the 4 items listed above? When would each be used? What the heck is an ActiveX control and how is it different from other controls?

 

[NateO]

Re: What is the difference between these objects?

I am having difficulty writing object-based code for a worksheet because I don't understand the difference between these references:

OLEObject
Object
Shape
Control

They all seem to be the same thing, but there is obviously some differences. I have tried playing around with different code to see what the differences are, but I keep getting errors. What are the differences between the 4 items listed above? When would each be used? What the heck is an ActiveX control and how is it different from other controls?

An object is like most things in Excel, e.g., range, worksheet, control.

The type of a control is msoOLEControlObject.

Check this out, see if it helps.

What, more specifically, is the damage?

 

[Jaafar Tribak]

Hi Phantom,

You are absolutely right.These objects can a be a real pain when using them in code. :x

** Basically, a shape is any object that is placed in the Drawing layer of a worksheet .Therefore,Controls,Pictures,Autoshapes or embeeded objects from other applications "like Word" are all Shapes objects members of the Shapes Collection.

So,all the above objects are members of the Shapes Collection and you can refer to any of them through the Shape Object.

** An OleObject collection is a subsidiary of the Shape Collection.OleObjects differ from other shapes like Autoshapes in that they are linked objects.
A good example is an embeeded Word Document in an Excel WorkSheet.The good thing about OLEobjects is that you can manipulate them without leaving Excel.So in the example of the embeeded Word document,you can control all the Word Application objects and manipulate the Document through VBA without leaving the Excel application.

One thing to remember is that the Shapes and OLEObjects Collections overlap:Each object in the OLEObjects Collection is also contained in the Shapes Collection ,and every OLE object in the shapes Collection is also contained in the OLEObjects Collection.

** The ActiveX Control is also an OLE Object as opposed to a control from the Forms Toolbar which is a shape.

Let's take an example.Say we have a sheet on which we have placed different objects:Autoshapes;ActiveX Controls;Pictures and a Word Document.

The following code will show all the names of all the Objects on the worksheet :

For Each c In Sheets(1).Shapes
MsgBox c.Name
Next

On the other hand,the following code will only display the name of all the embeeded objects except for the AutoShape Objects:

For Each c In Sheets(1).OLEObjects
MsgBox c.Name
Next

Phantom1975,my advice is to take a look at a good reference book and study this subject (In Particular the OLE Concept) properly.I am sure it will give you an excellent understanding of how to refer to Worksheet Embeeded objects and on how to control other Applications from Excel .

I am myself,currently trying to make sense of all this OLE stuff.

Good Luck.

 

[phantom1975]

WONDERFUL explanation Jaafar! It certainly helps me to get a better understanding of what's going on. With Nate's post, I now how a good reference to do the reading. Thanks so much you two. PERFECT help.

 

[chander]

i hv got one problem with OLEObjects if any of u guy can help me out ..
I got some Some OLEObjects and/or Embedded objects in my workbook/sheet . I want to know its count and if there is even one object like form control , OLE object , embedded control , class/DLL library etc. i want to take some action.I am checking this by Putting a msgbox in Workbook_Open like

Msgbox Activeworkbook.Worksheets("Sheet1").OLEObjects.Count

and i put a command buttong and List box on Sheet and open workbook ..

The workbook event never fire when i open the workbook ... Not only Workbook_open event , even no event after tht gets fire... how i can come to know while opening my work book tht it has Form control or any other object in it so that i can take my action ...