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.Addres s

MsgBox ThisWorkbook.Names("MyRange").RefersToRange.Count

etc.

Rick


"michael.beckinsale" wrote in message
...
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

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


"michael.beckinsale" wrote in message
...
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

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

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"michael.beckinsale" wrote in message
...
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,

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


"michael.beckinsale" wrote in message
...
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

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

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




"michael.beckinsale" wrote:

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

--

Dave 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