Sheet1 object not Worksheets("Sheet1")

O

onedaywhen

I create a new workbook containing three worksheets, look in the VBE
and see four objects: ThisWorkbook, Sheet1, Sheet2 and Sheet3. Does
anyone know what these 'sheet' objects are? Typename(Sheet1) tells me
it's a Worksheet object but there seems to be more to it than that.

If I add a public property to the Sheet1 code module I can access it
in code with Sheet1.MyProperty but Worksheets("Sheet1").MyProperty
fails.

Is there a collection object that holds these 'sheet' objects? The
Sheets object sounds promising but appears to be a metacolletion that
includes Worksheet and Chart objects.

I want to define custom properties and methods at the worksheet level
and because these 'Sheet' objects have class modules I though I could
use them. However, without a collection object they may be of no use
to me after all. I could define my own classes that include a
Worksheet property and create an instance for each of my 30 worksheets
but it seems to be 're-inventing the wheel' a bit given these Sheet
object.

Can anyone shed any light on what these objects actually are?

Many thanks.
 
T

Tom Ogilvy

Like you say, they are classes for the three sheets and the workbook.

they are in the collection of vbComponents in the VBE for the project. set
a reference to the extensibility library to see them the object in the
object browser.

see Chip Pearson's page on the VBE for additional:

http://www.cpearson.com/excel/vbe.htm



Regards,
Tom Ogilvy
 
O

onedaywhen

Tom,
Thanks for your reply.

Although the Sheet1 is a member of the VBComponents collection it
still doesn't give me access to my custom property i.e.

ThisWorkbook.VBProject.VBComponents("Sheet1").MyProperty

MyProperty doesn't even appear in the Properties collection <g>.

I've discovered another thing: if I export the Sheet1 object and
import it again it's now just a class module.

Perhaps I'll never get my head round what this Sheet1 object actually
is. I guess I should heed the general advise and use Sheet1 for
worksheet events only.
 
Top