Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 excel workbooks open up from 1 file; named :1 and :2 - why? | Excel Discussion (Misc queries) | |||
Link to Access & Named Range | Excel Worksheet Functions | |||
Named Range: Same Names, Multiple Workbooks with Same Sheet Name | Excel Discussion (Misc queries) | |||
Strange named range issue with multiple workbooks | Excel Discussion (Misc queries) | |||
Get the name of all workbooks open from access | Excel Programming |