Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Referencing a Named Cell Range in another Workbook
I have a VBA application (in Workbook2) that reads and changes information
in a Named Cell in a user's Workbook (WorkBook1). The name "UserTotal" has been Insert/Name/Define defined in Workbook1 as =Sheet1!$A$1. The VBA code works fine whey I explicitly reference the sheet name in Workbook1 that the Name is defined as, but does not work if I only refer to Workbook 2. Since different users may have defined "MyTotal" on different sheets, is there a way of referring to the Named Cell without being explicit as to what cell it is on, something like: MyValue = Workbook1.Range(UserTotal).value instead of MyValue = Workbook1.Sheet1.Range(UserTotal).value or lacking that, is there a simple way of setting another variable equal to the Wookbook.Worksheet name that a variable is defined as in a way that I can use that in th code line above? Any help would be appreciated. Thanks, Frank Hayes |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Referencing a Named Cell Range in another Workbook
Frank,
I guess this is because the range is a property of woeksheets, not workbooks. You could try Evaluate(Workbook1.Names("UserTotal").RefersTo) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Frank & Pam Hayes" wrote in message ... I have a VBA application (in Workbook2) that reads and changes information in a Named Cell in a user's Workbook (WorkBook1). The name "UserTotal" has been Insert/Name/Define defined in Workbook1 as =Sheet1!$A$1. The VBA code works fine whey I explicitly reference the sheet name in Workbook1 that the Name is defined as, but does not work if I only refer to Workbook 2. Since different users may have defined "MyTotal" on different sheets, is there a way of referring to the Named Cell without being explicit as to what cell it is on, something like: MyValue = Workbook1.Range(UserTotal).value instead of MyValue = Workbook1.Sheet1.Range(UserTotal).value or lacking that, is there a simple way of setting another variable equal to the Wookbook.Worksheet name that a variable is defined as in a way that I can use that in th code line above? Any help would be appreciated. Thanks, Frank Hayes |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Referencing a Named Cell Range in another Workbook
Frank
Range is a property of a worksheet not a workbook, which is why missing out the workshet doesn't work. I use this: dim rng as range set rng = workbook1.names("UserTotal").referstoRange yourValue = rng.value etc... You need to be careful with names as they can be defined at the workbook level or the worksheet level and you can have the same name at both levels. This can create hard to find errors. My advice would be to make the names your code uses unusual so there is less chance of a clash. cheers simon -----Original Message----- I have a VBA application (in Workbook2) that reads and changes information in a Named Cell in a user's Workbook (WorkBook1). The name "UserTotal" has been Insert/Name/Define defined in Workbook1 as =Sheet1! $A$1. The VBA code works fine whey I explicitly reference the sheet name in Workbook1 that the Name is defined as, but does not work if I only refer to Workbook 2. Since different users may have defined "MyTotal" on different sheets, is there a way of referring to the Named Cell without being explicit as to what cell it is on, something like: MyValue = Workbook1.Range(UserTotal).value instead of MyValue = Workbook1.Sheet1.Range(UserTotal).value or lacking that, is there a simple way of setting another variable equal to the Wookbook.Worksheet name that a variable is defined as in a way that I can use that in th code line above? Any help would be appreciated. Thanks, Frank Hayes . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Referencing a Named Cell Range in another Workbook
Simon,
Thank you for the suggestion. It worked great. However, I am not able to do it in reverse. How would I write the code to change the value in "UserTotal" from VBA. set workbook1.names("UserTotal").value = 500 'did not work Thanks Frank |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Referencing a Named Cell Range in another Workbook
Thanks Bob,
Your suggestion worked. In addition, it allowed me to set the value in the Named Cell by using Evaluate(Workbook1.Names("UserTotal").RefersTo) = 500 Thanks, Frank "Bob Phillips" wrote in message ... Frank, I guess this is because the range is a property of woeksheets, not workbooks. You could try Evaluate(Workbook1.Names("UserTotal").RefersTo) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Frank & Pam Hayes" wrote in message ... I have a VBA application (in Workbook2) that reads and changes information in a Named Cell in a user's Workbook (WorkBook1). The name "UserTotal" has been Insert/Name/Define defined in Workbook1 as =Sheet1!$A$1. The VBA code works fine whey I explicitly reference the sheet name in Workbook1 that the Name is defined as, but does not work if I only refer to Workbook 2. Since different users may have defined "MyTotal" on different sheets, is there a way of referring to the Named Cell without being explicit as to what cell it is on, something like: MyValue = Workbook1.Range(UserTotal).value instead of MyValue = Workbook1.Sheet1.Range(UserTotal).value or lacking that, is there a simple way of setting another variable equal to the Wookbook.Worksheet name that a variable is defined as in a way that I can use that in th code line above? Any help would be appreciated. Thanks, Frank Hayes |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Referencing a Named Cell Range in another Workbook
Frank,
Interesting that. I have never tried it myself. One problem with it is that it doesn't work if the name doesn't already exist. I use this myself ActiveWorkbook.Names.Add Name:="UserTotal2", RefersTo:="=$A$1" -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Frank & Pam Hayes" wrote in message ... Thanks Bob, Your suggestion worked. In addition, it allowed me to set the value in the Named Cell by using Evaluate(Workbook1.Names("UserTotal").RefersTo) = 500 Thanks, Frank "Bob Phillips" wrote in message ... Frank, I guess this is because the range is a property of woeksheets, not workbooks. You could try Evaluate(Workbook1.Names("UserTotal").RefersTo) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Frank & Pam Hayes" wrote in message ... I have a VBA application (in Workbook2) that reads and changes information in a Named Cell in a user's Workbook (WorkBook1). The name "UserTotal" has been Insert/Name/Define defined in Workbook1 as =Sheet1!$A$1. The VBA code works fine whey I explicitly reference the sheet name in Workbook1 that the Name is defined as, but does not work if I only refer to Workbook 2. Since different users may have defined "MyTotal" on different sheets, is there a way of referring to the Named Cell without being explicit as to what cell it is on, something like: MyValue = Workbook1.Range(UserTotal).value instead of MyValue = Workbook1.Sheet1.Range(UserTotal).value or lacking that, is there a simple way of setting another variable equal to the Wookbook.Worksheet name that a variable is defined as in a way that I can use that in th code line above? Any help would be appreciated. Thanks, Frank Hayes |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing a named range on another sheet | Excel Worksheet Functions | |||
referencing a 3D Named Range | Excel Worksheet Functions | |||
Referencing a named range based upon Range name entry in cell | Excel Worksheet Functions | |||
referencing a named range using a variable | Excel Programming | |||
Referencing Named Range in Other Sheet | Excel Programming |