Global name used in local version
 

Hi all,

I define a global scope name, It works fine for all the other worksheets
except one.
in the error worksheet, I can see the global name in the formula bar
dropdwon, when I click it, I am lead to the right location on the right
sheet, but when I use the name as a validation on a cell, I am lead to a
location on this error worksheet but with the correct row and column number

Clara
--
thank you so much for your help

Global name used in local version
 

I am a little unclear exactly what is going on on your sheet but here is a
general explanation of named reange scope. There are two kinds of named
ranges (for the purpose of this dsicussion). Global named ranges are attached
to the workbook and are accessable to any sheet in the book. A local named
range is attached to a specific sheet and it applies only to that sheet.
Where a global and a local named range share the same name the local named
range takes precidence. So if on yoru error work sheet you have a named range
that matches the global named range then it takes precidence which might
explain your difficulties.

If you do not use the name manager download to manage your named ranges it
is well worth getting a copy...
http://www.oaltd.co.uk/MVP/

When you use named ranges in VBA things get a shade more complicated as a
worksheet must be specified before you can define a range so named ranges
require being explicit with the sheet where the named range comes from...

If I have missed the mark with your question just let me know...
--
HTH...

Jim Thomlinson


"clara" wrote:

Hi all,

I define a global scope name, It works fine for all the other worksheets
except one.
in the error worksheet, I can see the global name in the formula bar
dropdwon, when I click it, I am lead to the right location on the right
sheet, but when I use the name as a validation on a cell, I am lead to a
location on this error worksheet but with the correct row and column number

Clara
--
thank you so much for your help

Global name used in local version
 

Hi Jim,

the following is my summary of the problem,

there are many sheets in my workbook, two of them are relevant to the
problem, there are some names defined on sheet A, and I make them all global,
on sheet B, I use the names as the sources of validation. On other sheets I
use the same way, they all works well. But on Sheet B, I can use the
formularbar's dropdown to go to the defined names on sheet A, but when I use
the name as a source of validation, the name can not lead me to the Sheet A,
but instead it will lead me to the same range on the sheet B.

I use the following Macro to delete all local names on sheet B.

Sub Macro6()
Dim oName As Name

For Each oName In ActiveSheet.Names
oName.Delete
Next

End Sub

so, on sheet B there are only global names.

Clara
--
thank you so much for your help


"Jim Thomlinson" wrote:

I am a little unclear exactly what is going on on your sheet but here is a
general explanation of named reange scope. There are two kinds of named
ranges (for the purpose of this dsicussion). Global named ranges are attached
to the workbook and are accessable to any sheet in the book. A local named
range is attached to a specific sheet and it applies only to that sheet.
Where a global and a local named range share the same name the local named
range takes precidence. So if on yoru error work sheet you have a named range
that matches the global named range then it takes precidence which might
explain your difficulties.

If you do not use the name manager download to manage your named ranges it
is well worth getting a copy...
http://www.oaltd.co.uk/MVP/

When you use named ranges in VBA things get a shade more complicated as a
worksheet must be specified before you can define a range so named ranges
require being explicit with the sheet where the named range comes from...

If I have missed the mark with your question just let me know...
--
HTH...

Jim Thomlinson


"clara" wrote:

Hi all,

I define a global scope name, It works fine for all the other worksheets
except one.
in the error worksheet, I can see the global name in the formula bar
dropdwon, when I click it, I am lead to the right location on the right
sheet, but when I use the name as a validation on a cell, I am lead to a
location on this error worksheet but with the correct row and column number

Clara
--
thank you so much for your help

Global name used in local version
 

What does the name manager addin show you about the named ranges in question.
--
HTH...

Jim Thomlinson


"clara" wrote:

Hi Jim,

the following is my summary of the problem,

there are many sheets in my workbook, two of them are relevant to the
problem, there are some names defined on sheet A, and I make them all global,
on sheet B, I use the names as the sources of validation. On other sheets I
use the same way, they all works well. But on Sheet B, I can use the
formularbar's dropdown to go to the defined names on sheet A, but when I use
the name as a source of validation, the name can not lead me to the Sheet A,
but instead it will lead me to the same range on the sheet B.

I use the following Macro to delete all local names on sheet B.

Sub Macro6()
Dim oName As Name

For Each oName In ActiveSheet.Names
oName.Delete
Next

End Sub

so, on sheet B there are only global names.

Clara
--
thank you so much for your help


"Jim Thomlinson" wrote:

I am a little unclear exactly what is going on on your sheet but here is a
general explanation of named reange scope. There are two kinds of named
ranges (for the purpose of this dsicussion). Global named ranges are attached
to the workbook and are accessable to any sheet in the book. A local named
range is attached to a specific sheet and it applies only to that sheet.
Where a global and a local named range share the same name the local named
range takes precidence. So if on yoru error work sheet you have a named range
that matches the global named range then it takes precidence which might
explain your difficulties.

If you do not use the name manager download to manage your named ranges it
is well worth getting a copy...
http://www.oaltd.co.uk/MVP/

When you use named ranges in VBA things get a shade more complicated as a
worksheet must be specified before you can define a range so named ranges
require being explicit with the sheet where the named range comes from...

If I have missed the mark with your question just let me know...
--
HTH...

Jim Thomlinson


"clara" wrote:

Hi all,

I define a global scope name, It works fine for all the other worksheets
except one.
in the error worksheet, I can see the global name in the formula bar
dropdwon, when I click it, I am lead to the right location on the right
sheet, but when I use the name as a validation on a cell, I am lead to a
location on this error worksheet but with the correct row and column number

Clara
--
thank you so much for your help