 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / May 2008refer to a named range in this.workbook
Hi All, How do l refer to a workbook level named range? I was expecting to use something like this: ThisWorkbook.Names = "MyRange" Regards Michael This is the actual range... ThisWorkbook.Names("MyRange").RefersToRange so you can reference any of it properties; for example... MsgBox ThisWorkbook.Names("MyRange").RefersToRange.Address MsgBox ThisWorkbook.Names("MyRange").RefersToRange.Count etc. Rick > Hi All, > [quoted text clipped - 7 lines] > > Michael Hi Rick, Thanks for the quick response. Unfortunately that doesn't seem to solve the problem. Let me explain a little more. I am building a workbook which contains a form and a 'list' sheet for info to populate the form's combo boxs. The form is to be used to populate any other ad-hoc workbook the user may want. Simply put an 'add-in' type workbook is used to collate the information but the data is entered into another workbook. This is example of code l have tried to populate the combo boxes using the named range UserForm_Initialize cbFUNCT.RowSource = ThisWorkbook.Names("FUNCT").RefersToRange and cbFUNCT.RowSource = ThisWorkbook.Names("FUNCT") Any ideas? Regards Michael Hi Rick, Sorry, obviously being an idiot this morning. I didnt specify the property, in this case .address Regards Michael The RowSource property is a String, the RefersToRange is a Range... you can't assign a range to a String. I'm guessing you may want to do this... cbFUNCT.RowSource = ThisWorkbook.Names("FUNCT").RefersToRange.Address where you pickup the actual address from the range. Remember, the RefersToRange is the actual Range that the Name references. Rick > Hi Rick, > [quoted text clipped - 25 lines] > > Michael No sooner than I sent my message, your follow up (recognizing the problem) appeared. Rick > The RowSource property is a String, the RefersToRange is a Range... you > can't assign a range to a String. I'm guessing you may want to do this... [quoted text clipped - 35 lines] >> >> Michael Hi Rick, Hope you are still monitoring. This still doesn't work. The .Address property seems to return the address without the sheet reference: A1:A2 If l use .RefersTo using a message box it returns the correct property Lists! A1:A2 But i can then assign that to the RowSource Any ideas? Regards Michael If ThisWorkbook is the activeworkbook what you posted previously should work cbFUNCT.RowSource = ThisWorkbook.Names("FUNCT") Otherwise try this cbFUNCT.RowSource = _ ThisWorkbook.Names("FUNCT").RefersToRange.Address(, , , True) Regards, Peter T > Hi Rick, > [quoted text clipped - 19 lines] > > Michael An alternative to Peter's (just using named parms): cbFUNCT.RowSource _ = ThisWorkbook.Names("FUNCT").RefersToRange.Address(external:=true) > Hi Rick, > [quoted text clipped - 19 lines] > > Michael  SignatureDave Peterson Hi All, Sorry but that doesn't work either. Maybe l am missing some thing re ThisWorkbook ThisWorkbook contains the form, code, named range etc The active workbook can be any other workbook. Any further ideas greatly appreciated. Regards Michael Hi All, I must take back all i said. Works perfectly, typo error Thanks tremendously, this was really holding me up. Its always what appears to be easy that proves a problem! Regards Michael |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.