refer to a named range in this.workbook

  • Thread starter Thread starter michael.beckinsale
  • Start date Start date
M

michael.beckinsale

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 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
 
No sooner than I sent my message, your follow up (recognizing the problem)
appeared.

Rick
 
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
 
An alternative to Peter's (just using named parms):

cbFUNCT.RowSource _
= ThisWorkbook.Names("FUNCT").RefersToRange.Address(external:=true)
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with progamatically defined named ranges 3
Code to delete names works in one WB, but not another WB? 2
AutoFilter Criteria using named range 4
using range name in Find 2
Problem using Range objects 1
How to get named range value? 5
Create Sheets from a Range and Copy Data on copy Data on newlycreated Sheets with the Sheet Names 2
Referering sheet name of a named cell 1

Back
Top