Why can you only access a shape properties when the sheet with the shape on is activated?

[Johnny C]

Just idly curious. I can't access a shapes properties unless the sheet is activated. I'm guessing the process of activating the sheet loads the sheet shapes into accessible memory, but why? Are the sheet's shapes and properties stored in an abbreviated form that is only expanded to accessible memory when the sheet is activated?

Or should I be accessing a worksheet shape cache to get the shapes (like the pivot table cache)

 

[My Aswer Is This]

You can do it like this:
Sheets(1).Shapes("Oval 1").Fill.ForeColor.RGB = RGB(0, 0, 255)

 

[Johnny C]

Does it only work with the sheet codenumber?

When I reference a sheet shape e.g.

strHTMLText = Workbooks(strThisWB).wkSettings.Shapes("strEmailText").Text

it gives an error, Error 438, Object doesn't support this property or method
I've tried all sorts.

Workbooks(strThisWB).Activate
wkSettings.Activate
strHTMLText = ActiveSheet.Shapes("strEmailText").Text

works fine, but

strHTMLText = Workbooks(strThisWB).wkSettings.Shapes("strEmailText").Text

bombs

 

[RoryA]

What is wkSettings? A variable, or sheet code name? Either way, you can't use Workbooks(strThisWB).wkSettings since a workbook doesn't have a wkSettings property, and you don't need to since it refers to a specific worksheet anyway so it's just:

strHTMLText = wkSettings.Shapes("strEmailText").Text

 

[Johnny C]

wkSettings is a sheet code name.

It's nestled amongst code for XL/PP & Word so I'll have a play around with it.

 

[RoryA]

If it's a code name, it would only work for a sheet in the workbook with the code (unless you'd set a reference to another workbook, in which case you could use projectname.codename to refer to a sheet in that workbook) so there is no need to qualify it anyway.