Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sheets("Sheet1").Cells.ClearContents | Excel Discussion (Misc queries) | |||
Active sheet instead of a "sheet1" for Macro | Excel Worksheet Functions | |||
how do i convert this to a formula +"sheet1!J"&(sheet2!A30)-1 | Excel Worksheet Functions | |||
how do copy "sheet1!A1+1 in sheet2 to sheet 3 and get "sheet2!A1+ | Excel Discussion (Misc queries) | |||
How do I color code the "SHEET1" Tab in Excel? | Excel Worksheet Functions |