Access Named range value with multiple workbooks open
 

I am trying to access the value in a single cell that is a named range.

Code:

If Workbooks(Current).Names("UpDate_File_Name1").Valu e = "Save to" then
....

When I debug the .Value I get the cell address not the cell value; in
this case:

=Setup!$C$3 instead of "Save to" which is the value in the cell.

I can do:

If Activeworkbook.Worksheets("Setup").cells(3,3).valu e = "Save to"
then...

or

If Workbooks(Current).Worksheets("Setup").Cells(3, 3).Value = "Save to"
then...

and get the Value to be: "Save to"

I would like to use Named ranges in the VBA code.

Ken

Access Named range value with multiple workbooks open
 

Perhaps
Workbooks(Current).Names("UpDate_File_Name1").Refe rsToRange.Value

or
Range("Update_File_Name1").Value


" wrote:

I am trying to access the value in a single cell that is a named range.

Code:

If Workbooks(Current).Names("UpDate_File_Name1").Valu e = "Save to" then
....

When I debug the .Value I get the cell address not the cell value; in
this case:

=Setup!$C$3 instead of "Save to" which is the value in the cell.

I can do:

If Activeworkbook.Worksheets("Setup").cells(3,3).valu e = "Save to"
then...

or

If Workbooks(Current).Worksheets("Setup").Cells(3, 3).Value = "Save to"
then...

and get the Value to be: "Save to"

I would like to use Named ranges in the VBA code.

Ken

Access Named range value with multiple workbooks open
 

This suggestion
Range("Update_File_Name1").Value
I believe will only work if the workbook containing that named range is the
activeworkook.

It appears the worksheet containing the named range would also have to be
qualified if the named range is in a workbook that is not active:
Workbooks(current).Worksheets("Sheet1").Range("UpD ate_File_Name1").Value

The RefersToRange may be a safer bet.

"JMB" wrote:

Perhaps
Workbooks(Current).Names("UpDate_File_Name1").Refe rsToRange.Value

or
Range("Update_File_Name1").Value


" wrote:

I am trying to access the value in a single cell that is a named range.

Code:

If Workbooks(Current).Names("UpDate_File_Name1").Valu e = "Save to" then
....

When I debug the .Value I get the cell address not the cell value; in
this case:

=Setup!$C$3 instead of "Save to" which is the value in the cell.

I can do:

If Activeworkbook.Worksheets("Setup").cells(3,3).valu e = "Save to"
then...

or

If Workbooks(Current).Worksheets("Setup").Cells(3, 3).Value = "Save to"
then...

and get the Value to be: "Save to"

I would like to use Named ranges in the VBA code.

Ken

Access Named range value with multiple workbooks open
 

It is true the .Range substitution for .Name alone does not work.
Error.

The Range("Update_File_Name1").Value has the problem with having to be
the active workbook. As stated. Works but not with another workbook
active.

The Workbooks(Current).Names("UpDate_File_Name1").Refe rsToRange.Value
works perfectly.

I don't need the Worksheets("Sheet1"). quantifyer but will add it if I
expand beyond my current needs.

All very helpful. Impressive.........

Thanks,

Ken


JMB wrote:
This suggestion
Range("Update_File_Name1").Value
I believe will only work if the workbook containing that named range is the
activeworkook.

It appears the worksheet containing the named range would also have to be
qualified if the named range is in a workbook that is not active:
Workbooks(current).Worksheets("Sheet1").Range("UpD ate_File_Name1").Value

The RefersToRange may be a safer bet.

"JMB" wrote:

Perhaps
Workbooks(Current).Names("UpDate_File_Name1").Refe rsToRange.Value

or
Range("Update_File_Name1").Value


" wrote:

I am trying to access the value in a single cell that is a named range.

Code:

If Workbooks(Current).Names("UpDate_File_Name1").Valu e = "Save to" then
....

When I debug the .Value I get the cell address not the cell value; in
this case:

=Setup!$C$3 instead of "Save to" which is the value in the cell.

I can do:

If Activeworkbook.Worksheets("Setup").cells(3,3).valu e = "Save to"
then...

or

If Workbooks(Current).Worksheets("Setup").Cells(3, 3).Value = "Save to"
then...

and get the Value to be: "Save to"

I would like to use Named ranges in the VBA code.

Ken