Sheet1 object not Worksheets("Sheet1")
 

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.

Sheet1 object not Worksheets("Sheet1")
 

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



"onedaywhen" wrote in message
om...
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.

Sheet1 object not Worksheets("Sheet1")
 

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").MyPr operty

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.

"Tom Ogilvy" wrote in message ...
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



"onedaywhen" wrote in message
om...
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.