try to use a worksheet object

[Guest]

I must be missing a concept here

Why does this not work

Dim FY As Strin
Dim chartsheet as workshee

Set ChartSheet = Sheets("PORChart"

FY = ChartSheet.txtF

When this does work

Dim FY As Strin

FY = Sheets("PORChart").txtF

Call code is executed form a separate code module

Any explanation would be great

Thank

 

[Tom Ogilvy]

? sheet3.MyVar
$B$3
? worksheets("Sheet3").Myvar
$B$3
set sh = Worksheets("Sheet3")
? sh.Myvar
$B$3

all worked for me.

 

[George Nicholson]

The Sheets collection contains 2 kinds of distinct objects: Worksheets and
Charts (or "Chart Sheets": don't confuse Chart Sheets with Chart objects
that can also exist on Worksheets.).

Example 1 doesn't work because you defined your object variable explicitly
as a worksheet and then tried to assign (what I assume is) a Chart sheet to
it. A pure and simple Type Mismatch.

Your 2nd example works because it avoids the Worksheet vs.Chart sheet issue
and simply points to a member of the Sheets collection.

--

HTH,
George Nicholson

Remove 'Junk' from return address.

 

[Guest]

Actually

"ChartSheet" is just what I happend to call that worksheet (obviouslly a poor choice in names). "Chartsheet" is in fact a worksheet with cells. No chart

So I'm still a little confused.