ARRG! Names not showing up, can't use in validation or ListBox??!

[Ed from AZ]

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

 

[Peter T]

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]

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

 

[Peter T]

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
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


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

 

[Ed from AZ]

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