using a global named range in worksheet_change

[stuck]

If I create a simple macro in a regular module to insert a formula into a cell, akin to:

Code: Select all

Sub insertAnnotation()
        Range("L6").Formula = "=" & Range("namedRange").Text
End Sub

then, as expected, running that one liner adds an = sign to the beginning of the text of a formula held in the cell defined by 'namedRange' and inserts that as a formula in cell L6. So far so good.

If I move the one liner to the code for the sheet and put it into Worksheet_Change and add some bells and whistles to trigger the insertion of the formula when another cell is changed:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
     Dim triggerCell As Range

     Set triggerCell = Range("L4")
 
     If Not Application.Intersect(triggerCell, Range(Target.Address)) _
               Is Nothing Then
          Range("L6").Formula = "=" & Range("namedRange").Text
    End If
    
End Sub

then it fails at Range("L6")... with
    Run-time error '1004':
    method 'Range' of object'_Worksheet' failed
which I take to mean it can't interpret 'namedRange' as a range because, even though it s a global named range, 'namedRange' is a cell on another sheet in the book. I don't want to move the named range off the sheet where it is.

So how do I workaround this?

Ken

[HansV]

In worksheet event procedures such as Worksheet_Change, Range(...) automatically refers to the worksheet that the code belongs to, unless you explicitly specify Worksheets(...).Range(...)

Since you are using a defined name, you can use

Code: Select all

          Range("L6").Formula = "=" & ThisWorkbook,Names("namedRange").RefersToRange.Text

That way, you don't have to specify the name of the worksheet.

[stuck]

...unless you explicitly specify Worksheets(...).Range(...)...

probably the one thing I didn't think of trying before I asked here. Yes, that makes it work.

...Since you are using a defined name, you can use...

That works as well. Two fixes for the price of one! That deserves a lunch time



Ken

[HansV]

Thanks

(It'll be an after-lunch for me )