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!

 

[RoryA]

In a worksheet code module, Range defaults to the sheet containing the code. I'm guessing that sheet does not contain your named range, so use:

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

 

[Aid2Kids]

THANK YOU for your prompt and most helpful response!