In XL 2007, I'm naming ranges using
nm = wks.Name
a = a - 1
rg = wks2.Range(Cells(21, b), Cells(a, b))
wks2.Names.Add _
Name:=nm, _
RefersTo:=rg, _
Visible:=True

I can see them in the Name Manager, but I can not see them in the
Names box (upper left), nor can I reference them as a Data Validation
source or a ListBox source.

What I really want to do is use two ListBoxes, with LB2 taking its
reference from the selection of LB1. I found this from Debra
Dalgleish:

Create a change event for ListBox1, with code similar to the
following:

Private Sub ListBox1_Change()
ListBox2.ListFillRange = ListBox1.Value
End Sub

but I can't get the ListBox I created on the worksheet to accept the
named range as a source!!

Can someone help me make sense of this?

Ed

You are defining a worksheet level name, that will only be visible in the
names box on its own sheet. You can use it in other sheets by prefixing
like this

='Sheet name'!theName

the apostrophes may be required if there is punctuation or some other
characters like a space in the sheet name.

You should be able to use in a listbox on other sheets by prefixing as above

You can't refer to other sheets in a DV list but you can work around like
this -
=INDIRECT("sheet1!theName")

Regards,
Peter T


"Ed from AZ" wrote in message
...
In XL 2007, I'm naming ranges using
nm = wks.Name
a = a - 1
rg = wks2.Range(Cells(21, b), Cells(a, b))
wks2.Names.Add _
Name:=nm, _
RefersTo:=rg, _
Visible:=True

I can see them in the Name Manager, but I can not see them in the
Names box (upper left), nor can I reference them as a Data Validation
source or a ListBox source.

What I really want to do is use two ListBoxes, with LB2 taking its
reference from the selection of LB1. I found this from Debra
Dalgleish:

Create a change event for ListBox1, with code similar to the
following:

Private Sub ListBox1_Change()
ListBox2.ListFillRange = ListBox1.Value
End Sub

but I can't get the ListBox I created on the worksheet to accept the
named range as a source!!

Can someone help me make sense of this?

Ed

Hi, Peter. Thanks for helping. I need to get this solved today!!

The worksheet is named "DataEntry". At this time, I am creating three
named ranges in DataEntry using the code previously posted. I am
trying to use these names for a data validation drop-down list in
cells in DataEntry. (I'm no longer worrying about a ListBox.)

The names box on DataEntry does not show these names. Validation
using these names evaluates to an error. If I manually name the
ranges to the scope of the worksheet, they show up and I can use them.

What am I missing from the code to create a named range I can use?

Ed


On Dec 16, 9:10*am, "Peter T" <peter_t@discussions wrote:
You are defining a worksheet level name, that will only be visible in the
names box on its own sheet. *You can use it in other sheets by prefixing
like this

='Sheet name'!theName

the apostrophes may be required if there is punctuation or some other
characters like a space in the sheet name.

You should be able to use in a listbox on other sheets by prefixing as above

You can't refer to other sheets in a DV list but you can work around like
this -
=INDIRECT("sheet1!theName")

Regards,
Peter T

"Ed from AZ" wrote in ...



In XL 2007, I'm naming ranges using
* * *nm = wks.Name
* * *a = a - 1
* * *rg = wks2.Range(Cells(21, b), Cells(a, b))
* * *wks2.Names.Add _
* * * *Name:=nm, _
* * * *RefersTo:=rg, _
* * * *Visible:=True

I can see them in the Name Manager, but I can not see them in the
Names box (upper left), nor can I reference them as a Data Validation
source or a ListBox source.

What I really want to do is use two ListBoxes, with LB2 taking its
reference from the selection of LB1. *I found this from Debra
Dalgleish:

* Create a change event for ListBox1, with code similar to the
following:

* Private Sub ListBox1_Change()
* * *ListBox2.ListFillRange = ListBox1.Value
* End Sub

but I can't get the ListBox I created on the worksheet to accept the
named range as a source!!

Can someone help me make sense of this?

Ed- Hide quoted text -

- Show quoted text -

You haven't posted all your actual code so it's difficult to say if there's
something wrong, I assume you've just typed some snippets.

Just looking at what you have posted would need to change

rg = wks2.Range(Cells(21, b), Cells(a, b))
to
Set rg = wks2.Range(Cells(21, b), Cells(a, b))

Other things, wks2 would need to correctly refer to a worksheet (having used
the Set statement) and a & b would need to be valid row/col numbers.

It's probably better not to name the name same as the sheet.

Regards,
Peter T


"Ed from AZ" wrote in message
...
Hi, Peter. Thanks for helping. I need to get this solved today!!

The worksheet is named "DataEntry". At this time, I am creating three
named ranges in DataEntry using the code previously posted. I am
trying to use these names for a data validation drop-down list in
cells in DataEntry. (I'm no longer worrying about a ListBox.)

The names box on DataEntry does not show these names. Validation
using these names evaluates to an error. If I manually name the
ranges to the scope of the worksheet, they show up and I can use them.

What am I missing from the code to create a named range I can use?

Ed


On Dec 16, 9:10 am, "Peter T" <peter_t@discussions wrote:
You are defining a worksheet level name, that will only be visible in the
names box on its own sheet. You can use it in other sheets by prefixing
like this

='Sheet name'!theName

the apostrophes may be required if there is punctuation or some other
characters like a space in the sheet name.

You should be able to use in a listbox on other sheets by prefixing as
above

You can't refer to other sheets in a DV list but you can work around like
this -
=INDIRECT("sheet1!theName")

Regards,
Peter T

"Ed from AZ" wrote in
...



In XL 2007, I'm naming ranges using
nm = wks.Name
a = a - 1
rg = wks2.Range(Cells(21, b), Cells(a, b))
wks2.Names.Add _
Name:=nm, _
RefersTo:=rg, _
Visible:=True

I can see them in the Name Manager, but I can not see them in the
Names box (upper left), nor can I reference them as a Data Validation
source or a ListBox source.

What I really want to do is use two ListBoxes, with LB2 taking its
reference from the selection of LB1. I found this from Debra
Dalgleish:

Create a change event for ListBox1, with code similar to the
following:

Private Sub ListBox1_Change()
ListBox2.ListFillRange = ListBox1.Value
End Sub

but I can't get the ListBox I created on the worksheet to accept the
named range as a source!!

Can someone help me make sense of this?

Ed- Hide quoted text -

- Show quoted text -

Thanks for helping, Peter. I managed to get it working. Not sure
how, but it's doing what it's supposed to. I'll examine it later.

Ed

On Dec 16, 10:48*am, "Peter T" <peter_t@discussions wrote:
You haven't posted all your actual code so it's difficult to say if there's
something wrong, I assume you've just typed some snippets.

Just looking at what you have posted would need to change

rg = wks2.Range(Cells(21, b), Cells(a, b))

to
Set rg = wks2.Range(Cells(21, b), Cells(a, b))

Other things, wks2 would need to correctly refer to a worksheet (having used
the Set statement) and a & b would need to be valid row/col numbers.

It's probably better not to name the name same as the sheet.

Regards,
Peter T

"Ed from AZ" wrote in ...
Hi, Peter. *Thanks for helping. *I need to get this solved today!!

The worksheet is named "DataEntry". *At this time, I am creating three
named ranges in DataEntry using the code previously posted. *I am
trying to use these names for a data validation drop-down list in
cells in DataEntry. *(I'm no longer worrying about a ListBox.)

The names box on DataEntry does not show these names. *Validation
using these names evaluates to an error. *If I manually name the
ranges to the scope of the worksheet, they show up and I can use them.

What am I missing from the code to create a named range I can use?

Ed

On Dec 16, 9:10 am, "Peter T" <peter_t@discussions wrote:



You are defining a worksheet level name, that will only be visible in the
names box on its own sheet. You can use it in other sheets by prefixing
like this

='Sheet name'!theName

the apostrophes may be required if there is punctuation or some other
characters like a space in the sheet name.

You should be able to use in a listbox on other sheets by prefixing as
above

You can't refer to other sheets in a DV list but you can work around like
this -
=INDIRECT("sheet1!theName")

Regards,
Peter T

"Ed from AZ" wrote in
...

In XL 2007, I'm naming ranges using
nm = wks.Name
a = a - 1
rg = wks2.Range(Cells(21, b), Cells(a, b))
wks2.Names.Add _
Name:=nm, _
RefersTo:=rg, _
Visible:=True

I can see them in the Name Manager, but I can not see them in the
Names box (upper left), nor can I reference them as a Data Validation
source or a ListBox source.

What I really want to do is use two ListBoxes, with LB2 taking its
reference from the selection of LB1. I found this from Debra
Dalgleish:

Create a change event for ListBox1, with code similar to the
following:

Private Sub ListBox1_Change()
ListBox2.ListFillRange = ListBox1.Value
End Sub

but I can't get the ListBox I created on the worksheet to accept the
named range as a source!!

Can someone help me make sense of this?

Ed- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -