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" <(E-Mail Removed)> wrote in message
news:f6011637-1fb7-427b-9f9a-(E-Mail Removed)...
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" <prof_ofw...@yahoo.com> wrote in
> messagenews:f805f563-aaae-41d2-b25b-(E-Mail Removed)...
>
>
>
> > 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 -
Similar Threads
