Hi,

I would like to "localise" range names using VBA code.

Local range names (as I understand) are range names that are attached to the
sheet name on which they reside - so you can have several of the same range
names, but each one on a different sheet in the same workbook.

These range names have the sheet name infront of them separated by a "!"

e.g. sheet1!range1

I could, of course, create a string from the individual components and then
assign the address of cells represented by range1 to it - but it wouldn't be
very neat....

I just want to take range1 and localise it.

Ideas...?? Thanks...Chris

Hi Chris,

Code to do that would go something like this

-loop names at workbook-level
if InStr(nm.Name, "!") ignore, it's already a local name
set rng = nm.RefersToRange
if this succeeds (it's a range name), add a new similar name prefixed with
apostrophe & rng.parent.name & apostrophe & ! & nm.name refersto rng
(also having checked same local name doesn't already exist)
delete the old name

But -
- there are loads of pitfalls
- all the hard work has been done very well !

Get hold of the NameManager addin which you can get from the authors' sites
of Jan Karel Pieterse and Charles Williams (download sections).

www.jkp-ads.com
www.DecisionModels.com

Regards,
Peter T


"Chris Gorham" wrote in message
...
Hi,

I would like to "localise" range names using VBA code.

Local range names (as I understand) are range names that are attached to
the
sheet name on which they reside - so you can have several of the same
range
names, but each one on a different sheet in the same workbook.

These range names have the sheet name infront of them separated by a "!"

e.g. sheet1!range1

I could, of course, create a string from the individual components and
then
assign the address of cells represented by range1 to it - but it wouldn't
be
very neat....

I just want to take range1 and localise it.

Ideas...?? Thanks...Chris

Thanks, I'll try this tomorrow....

I'm writing my own Range Name Manager...feel free to check out my website
www.mastertool.co.uk which contains all my add-ins...the linktracer is
amazing...

This version is the one currently being rewritten....

Chris

"Peter T" wrote:

Hi Chris,

Code to do that would go something like this

-loop names at workbook-level
if InStr(nm.Name, "!") ignore, it's already a local name
set rng = nm.RefersToRange
if this succeeds (it's a range name), add a new similar name prefixed with
apostrophe & rng.parent.name & apostrophe & ! & nm.name refersto rng
(also having checked same local name doesn't already exist)
delete the old name

But -
- there are loads of pitfalls
- all the hard work has been done very well !

Get hold of the NameManager addin which you can get from the authors' sites
of Jan Karel Pieterse and Charles Williams (download sections).

www.jkp-ads.com
www.DecisionModels.com

Regards,
Peter T


"Chris Gorham" wrote in message
...
Hi,

I would like to "localise" range names using VBA code.

Local range names (as I understand) are range names that are attached to
the
sheet name on which they reside - so you can have several of the same
range
names, but each one on a different sheet in the same workbook.

These range names have the sheet name infront of them separated by a "!"

e.g. sheet1!range1

I could, of course, create a string from the individual components and
then
assign the address of cells represented by range1 to it - but it wouldn't
be
very neat....

I just want to take range1 and localise it.

Ideas...?? Thanks...Chris

HI CHris,

I'm writing my own Range Name Manager...feel free to check out my website
www.mastertool.co.uk which contains all my add-ins...the linktracer is
amazing...

I'll check it out!

Regards,

Jan Karel Pieterse
Excel MVP
JKP Application Development Services
http://www.jkp-ads.com

please feel free to do so...but note that I'm 70% through a major upgrade.

I've looked at this localisation issue today...Peter's suggestion was really
a better way of coding my idea...but he has indicated via email that there
are problems ahead and indeed it appears that when a sheet name contains a
character such as "!" then it falls over. This is because Excel for some
reason starts putting the character ' at each end of the sheet name.

I've circuimvented this by always changing the sheet name to a temporary one
and then swopping it back...localisation then works fine using the code (or
close to it) from Peter.

Chris

" wrote:

HI CHris,

I'm writing my own Range Name Manager...feel free to check out my website
www.mastertool.co.uk which contains all my add-ins...the linktracer is
amazing...

I'll check it out!

Regards,

Jan Karel Pieterse
Excel MVP
JKP Application Development Services
http://www.jkp-ads.com