refer to a named range in this.workbook

[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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[michael.beckinsale]

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

 

[michael.beckinsale]

Hi Rick,

Sorry, obviously being an idiot this morning.

I didnt specify the property, in this case .address

Regards

Michael

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

No sooner than I sent my message, your follow up (recognizing the problem)
appeared.

Rick

 

[michael.beckinsale]

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

 

[Peter T]

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

 

[Dave Peterson]

An alternative to Peter's (just using named parms):

cbFUNCT.RowSource _
= ThisWorkbook.Names("FUNCT").RefersToRange.Address(external:=true)

 

[michael.beckinsale]

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

 

[michael.beckinsale]

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