[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Access Named range value with multiple workbooks open

ksgoodwin

12/12/2006 9:34:00 PM

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

Code:

If Workbooks(Current).Names("UpDate_File_Name1").Value = "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).value = "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
4 Answers

Jim Jackson

12/12/2006 10:03:00 PM

0

If I am not terribly off-course, the ".Names" is assigning the address.

Will it work if you substitute ".Range" for ".Names"?
--
Best wishes,

Jim


"ksgoodwin@gmail.com" 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").Value = "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).value = "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
>
>

JMB

12/13/2006 5:28:00 AM

0

Perhaps
Workbooks(Current).Names("UpDate_File_Name1").RefersToRange.Value

or
Range("Update_File_Name1").Value


"ksgoodwin@gmail.com" 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").Value = "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).value = "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
>
>

JMB

12/13/2006 5:47:00 AM

0

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("UpDate_File_Name1").Value

The RefersToRange may be a safer bet.

"JMB" wrote:

> Perhaps
> Workbooks(Current).Names("UpDate_File_Name1").RefersToRange.Value
>
> or
> Range("Update_File_Name1").Value
>
>
> "ksgoodwin@gmail.com" 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").Value = "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).value = "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
> >
> >

ksgoodwin

12/15/2006 1:25:00 AM

0

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").RefersToRange.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("UpDate_File_Name1").Value
>
> The RefersToRange may be a safer bet.
>
> "JMB" wrote:
>
> > Perhaps
> > Workbooks(Current).Names("UpDate_File_Name1").RefersToRange.Value
> >
> > or
> > Range("Update_File_Name1").Value
> >
> >
> > "ksgoodwin@gmail.com" 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").Value = "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).value = "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
> > >
> > >