Named range in a sheet referred to from another sheet

[Guest]

(Using Excel 2000 under Windows 98)

I have created a 1-cell named range by selecting cell A1 on Sheet1 and then
typing the name "MyCell" (without the quotes) in the names Litsbox.
Thereafter, I have typed this code in the click event of a command button
located in another sheet (Sheet2):

Range(ThisWorkbook.Names("MyName")).Value = 100

When I click on the button I get error 1004. However, if the button is in
Sheet1 it works fine. So how do I have to MyCell in the first case?

 

[Bob Phillips]

Try this

Application.Range("MyCell").Value = 100


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

It works, thanks a lot.

Still I'd like to know what was wrong with my syntax: I asume my named range
was "global" for the entire workbook and not just for the sheet the specific
cell was in.

I have loads of code lines using

Range(ThisWorkbook.Names(...

so I wonder if I should change them -as well as new references yet to be
written- in accordance with your suggestion, and so avoid trouble in the
future.