Thanks much, your answers are good food for thought.
After I posted the questions, and wrote a couple of macros dealing with the
the .Names collection, I'm going with the following summary approach that I
think strikes a "middle ground".
1. addin workbook named ranges will be like:
"range name" = "=sheetname!cell address[,!cell address]
2. a macro, during addin development will update the names collection and
remove the sheetname from the range formula:
"range name" = "=!cell address[,!cell address]
This makes the addin named ranges generic to any worksheet.
3. At user workbook open event, (user books are referenced to the addin),
a macro will update the user book's name collection from the addin
.Names collection.
If the App undergoes any change, the changes to the addin sheets, both
the sheets themselves and their named ranges, will "flow" to the user books.
I did not put the following fact in the original posting since it
really was not germaine to the "problem" you did not have time to look into,
but one
addin sheet, can be copied to many sheets in the user's book, so the cell
addresses must be the same in each user sheet.
Thanks again
Neal
--
Neal Z
"broro183" wrote:
hi Neal
1. Can you use named ranges to copy values from locked cells on a
protected sheet? I'm getting intermittent errors.
Yes, you can copy from locked cells on a protected sheet (may depend on
the protection settings). If you use a macro, which is reasonable within
an addin, you can unlock the sheet within the code & relock it or even
protect with "userinterfaceonly:= true". You can copy from a protected
sheet but can't necessarily paste into one, where are you pasting the
info to?
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)
You must unprotect before Inserting a defined Name (then you can
reprotect), but once you have created the Names once as part of the
addin/template development you shouldn't need to do it again.
I would use named ranges & copy the *values* (& whatever else is
wanted) from the addin sheet (rather than copying "everything").
Code:
--------------------
UserWs.range(sTotRng).value = Mdlws.range(sTotRng).value
--------------------
Are you rolling out a new template as a one off & modifying existing
formatting?
If not, I suggest developing & providing the complete template &
potentially copying the info from the user's file into a copy of the
standardised template. This ensures one consistent format &
minimises/removes risks of wrong values being transferred.
2. What can you tell me about why the commented line in the ExpenseAdd:
routine below did not work ?)
I would use the above approach once you get the naming convention right
to identify the 2 different files (sorry I haven't delved into this).
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.
Try & standardise your template as much as possible before release by
consulting end users & using best practice principles, such as those in
the below link, to minimise/eradicate changes in the layout. If you have
to move cells that are defined as constants/within Names, use [ctrl + x]
to cut the cell & paste it into the new location. This results in the
named range (for static names) being moved to the new location.
Spreadsheet design pdf, http://www.eusprig.org/smbp.pdf, sourced from:
'Recommended Spreadsheet (Excel) web sites, tools, groups, best
practice, financial modelling, modeling'
(http://www.sysmod.com/sslinks.htm)
hth
Rob
--
broro183
Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: http://www.thecodecage.com/forumz/member.php?userid=333
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=100652