1004 - Method 'Range' of object '_Worksheet' failed...

[Aid2Kids]

Greetings!

Excel 2010 (14.0.7190.5000) 64-bit
Win 8.1 Pro

I'm getting a 1004 error, "Method 'Range' of object '_Worksheet' failed", when I try to access a WORKBOOK-scope defined-name from a sheet's Worksheet_Activate() routine. Here's the code for the sheet...

Private Sub Worksheet_Activate()
     MsgBox "DefName contents = " & Range("TestDefName").Value
End Sub

This very code runs just fine from a code module, but not from the worksheet routine. Would someone be so kind as to give me a clue?


THANKS!

 

[Joe4]

It most likely means that:
- The named range "TestDefName" does not exist on that particular sheet
- The size of the range is more than one cell (you cannot use Range(...).Value on a multi-cell range).

 

[Aid2Kids]

THANK YOU Joe!