Hi All,
I am learning about named ranges, so please bear with me.
I have an Addin holding template sheets copied to the user's workbook.

I'm looking for the 'best' way to go forward as I'm having unanticipated
problems with the named ranges, and am at a point in time where
I have to pick one way to go.

1. Can you use named ranges to copy values from locked cells on a
protected sheet? I'm getting intermittent errors.

I just realized that the Insert Define Name menu path is grayed out
on a protected sheet. (Shoulda been a 'heads up' to me, but wasn't)


Up 'til now I've been using public constants in the addin such as:

Public Const sTotRng = "D27" ' users workbook is referenced to the addin.

and then using code like: varname = UserWs.range(sTotRng).value

I thought there was value in establishing a named range, versus changing
a lot of constants for cell ranges, but see below.

MdlWs is an addin template sheet.

Mdlws.range(sTotRng).Copy Destination:=UserWs.range(sTotRng)

has been working when refreshing the user's sheet for formats, etc.
The above also worked fine when copying from a protected sheet in the
addin.


2. What can you tell me about why the commented line in the ExpenseAdd:
routine below did not work ?

As a workaround, I wrote the function below it to give me the
equivalent of my public constant method, it worked, but then, why go thru all
the trouble ? Maybe my "constant" method is the way to go.

3. Over time, if the layout of the MdlWs templates changes, changing
the constants for cell addresses does not seem too different from
changing the named ranges.

Your thoughts ??

Thanks,
Neal


ExpenseAdd: 'Copy from model. money cell + literal.

' line below gets 1004 error, the value of gWktExpenLitRng is the
' named range. Lines below it were the workaround.

' MdlWs.Range(gWktExpenLitRng).Copy _
Destination:=UserWs.Range(gWktExpenLitRng)

CellAdr = sCellAdr_vsRefToF(Wbk.Names(gWktExpenLitRng).Refer sTo)
'CellAdr dimmed as string

MdlWs.Range(CellAdr).Copy Destination:=UserWs.Range(CellAdr)
Return


Public Function sCellAdr_vsRefToF(sRefersTo As String) As String

'Return plain address portion from "=!$a$1" .Names(xxxx).RefersTo
' also: "=!$a$1:$b$2,!$c$3:$d$4"

Dim Text As String, iByte As Integer

iByte = InStr(sRefersTo, "!")
Text = Right(sRefersTo, Len(sRefersTo) - iByte)
Text = Replace(Text, "!", "")
sCellAdr_vsRefToF = Text

End Function
--
Neal Z