Name a list in Excel 2003?

  • Thread starter Thread starter Alun Jones
  • Start date Start date
A

Alun Jones

In VBScript or C#, I can rename an excel ListObject by simply writing a line
such as:

ListObject lo = (ListObject)ws.ListObjects["List1"];
lo.Name = "Certificate List";

Now, how do I rename a list using the interactive interface, without code?

Thanks,

Alun.
~~~~
 
I looked and I couldn't find a way.

Maybe I looked in the wrong spot--or not enough spots, though.

But there are some things that can only be done via code. Maybe this is one of
them.

Barb said:
You may be able to find something under DATA -> LIST

Alun Jones said:
In VBScript or C#, I can rename an excel ListObject by simply writing a line
such as:

ListObject lo = (ListObject)ws.ListObjects["List1"];
lo.Name = "Certificate List";

Now, how do I rename a list using the interactive interface, without code?

Thanks,

Alun.
~~~~
Click to expand...
Click to expand...
 
Hi Alun

If the list has headers, then under Insert>Name>Define the list will be
the name of the first header in the list.
Select the name and give a new name for the range>OK. Delete the
original name.

If there are no headers, then Column1, Column2 etc will have been
inserted.
Insert Name>define>select column1 and give new name>OK. Delete Column 1
as Name.
 
I couldn't duplicate this in xl2003.

I put a bunch of test data (pretty much random stuff) in A1:F22 and then used
data|list and said my list had headers.

Then I did this in VBE's immediate window:
debug.Print activesheet.listobjects(1).name

I got this back:
List1

There were no characters in my range that contained the word List1.

I added a couple more lists--some with headers and some without and did more
debug.prints.

The list names looked like:
List2
and
List3

I used Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager:
(NameManager.Zip from http://www.oaltd.co.uk/mvp)

And the only name that showed up (and it was hidden) was:
Sheet1!_FilterDatabase

And interestingly, that moved depending on what the activecell was--and if it
was in a List.


Roger said:
Hi Alun

If the list has headers, then under Insert>Name>Define the list will be
the name of the first header in the list.
Select the name and give a new name for the range>OK. Delete the
original name.

If there are no headers, then Column1, Column2 etc will have been
inserted.
Insert Name>define>select column1 and give new name>OK. Delete Column 1
as Name.

--
Regards

Roger Govier

Alun Jones said:
In VBScript or C#, I can rename an excel ListObject by simply writing
a line such as:

ListObject lo = (ListObject)ws.ListObjects["List1"];
lo.Name = "Certificate List";

Now, how do I rename a list using the interactive interface, without
code?

Thanks,

Alun.
~~~~
Click to expand...
Click to expand...
 
hmmmm.....

I agree Dave.
However, start again on a blank sheet.
Immediately after creating the list, with the cursor within the list, go
to Insert>Name>Define
I then see the name of the first column, with a range that refers to the
List range. Clicking the icon at the end of the Refers to pane, selects
the range.

Do anything else, then return to Insert Name and the name disappears.

First time through, you can rename that range to another Name.
Very strange.

Tiredness now getting the better of me, but I will take this up again
later if you have any further thoughts/findings.
--
Regards

Roger Govier


Dave Peterson said:
I couldn't duplicate this in xl2003.

I put a bunch of test data (pretty much random stuff) in A1:F22 and
then used
data|list and said my list had headers.

Then I did this in VBE's immediate window:
debug.Print activesheet.listobjects(1).name

I got this back:
List1

There were no characters in my range that contained the word List1.

I added a couple more lists--some with headers and some without and
did more
debug.prints.

The list names looked like:
List2
and
List3

I used Jan Karel Pieterse's (with Charles Williams and Matthew Henson)
Name
Manager:
(NameManager.Zip from http://www.oaltd.co.uk/mvp)

And the only name that showed up (and it was hidden) was:
Sheet1!_FilterDatabase

And interestingly, that moved depending on what the activecell
was--and if it
was in a List.


Roger said:
Hi Alun

If the list has headers, then under Insert>Name>Define the list will
be
the name of the first header in the list.
Select the name and give a new name for the range>OK. Delete the
original name.

If there are no headers, then Column1, Column2 etc will have been
inserted.
Insert Name>define>select column1 and give new name>OK. Delete Column
1
as Name.

--
Regards

Roger Govier

Alun Jones said:
In VBScript or C#, I can rename an excel ListObject by simply
writing
a line such as:

ListObject lo = (ListObject)ws.ListObjects["List1"];
lo.Name = "Certificate List";

Now, how do I rename a list using the interactive interface,
without
code?

Thanks,

Alun.
~~~~
Click to expand...
Click to expand...
Click to expand...
 
Hi,

To create a Named definition

Do the following:

1. Enter different names of persons in cell G2:G6.
2. Select the range G2:G6
3. On the Insert menu, point to Name and then click Define. The Define Name
dialog box is displayed. The Refer To text box displays the selected range.
4. Enter a name in the Names in Workbook text box. This name is like a
variable, which stores all the cell values of the cell range
5. click the Add button to add the defined name in the Names in Workbook list.
6. Click OK. to save the setting and return the Worksheet.


To apply the named definition to cell or a range of cell.

Do the following:

1. On the Data menu, click Validation. The Validation dialog box is displayed.
2. Click the Setting Tab.
3. Under the Validation Criteria, clickthe Allow list and then select List.
4. In the Source text box enter the name you have defined by prefixing with
"="
5. Click and select the "The Ignore blank" and "In-cell drop down" check
boxes.
6. Click OK.

Note: You can modify the named range by either modifying directly in the
"Refer To" text box or Deleting the old named definition and add a same
definition name. You cannot directly over the name definition because the
Range definition in the Refer To text would still retain the same definition.

Challa Prabhu
 
I think that this is just excel's way of helping.

If you have any range selected, then the top left cell is used as a suggestion
for a new name (not existing, though) and the selected range's address is used
as a suggestion for the refers to string.

I think you just saw normal (helpful, <bg>) excel behavior for naming
ranges--not for naming the listobject itself.



Roger said:
hmmmm.....

I agree Dave.
However, start again on a blank sheet.
Immediately after creating the list, with the cursor within the list, go
to Insert>Name>Define
I then see the name of the first column, with a range that refers to the
List range. Clicking the icon at the end of the Refers to pane, selects
the range.

Do anything else, then return to Insert Name and the name disappears.

First time through, you can rename that range to another Name.
Very strange.

Tiredness now getting the better of me, but I will take this up again
later if you have any further thoughts/findings.
--
Regards

Roger Govier

Dave Peterson said:
I couldn't duplicate this in xl2003.

I put a bunch of test data (pretty much random stuff) in A1:F22 and
then used
data|list and said my list had headers.

Then I did this in VBE's immediate window:
debug.Print activesheet.listobjects(1).name

I got this back:
List1

There were no characters in my range that contained the word List1.

I added a couple more lists--some with headers and some without and
did more
debug.prints.

The list names looked like:
List2
and
List3

I used Jan Karel Pieterse's (with Charles Williams and Matthew Henson)
Name
Manager:
(NameManager.Zip from http://www.oaltd.co.uk/mvp)

And the only name that showed up (and it was hidden) was:
Sheet1!_FilterDatabase

And interestingly, that moved depending on what the activecell
was--and if it
was in a List.


Roger said:
Hi Alun

If the list has headers, then under Insert>Name>Define the list will
be
the name of the first header in the list.
Select the name and give a new name for the range>OK. Delete the
original name.

If there are no headers, then Column1, Column2 etc will have been
inserted.
Insert Name>define>select column1 and give new name>OK. Delete Column
1
as Name.

--
Regards

Roger Govier

In VBScript or C#, I can rename an excel ListObject by simply
writing
a line such as:

ListObject lo = (ListObject)ws.ListObjects["List1"];
lo.Name = "Certificate List";

Now, how do I rename a list using the interactive interface,
without
code?

Thanks,

Alun.
~~~~
Click to expand...
Click to expand...
Click to expand...
 
This is not what the OP asked about.

challa said:
Hi,

To create a Named definition

Do the following:

1. Enter different names of persons in cell G2:G6.
2. Select the range G2:G6
3. On the Insert menu, point to Name and then click Define. The Define Name
dialog box is displayed. The Refer To text box displays the selected range.
4. Enter a name in the Names in Workbook text box. This name is like a
variable, which stores all the cell values of the cell range
5. click the Add button to add the defined name in the Names in Workbook list.
6. Click OK. to save the setting and return the Worksheet.

To apply the named definition to cell or a range of cell.

Do the following:

1. On the Data menu, click Validation. The Validation dialog box is displayed.
2. Click the Setting Tab.
3. Under the Validation Criteria, clickthe Allow list and then select List.
4. In the Source text box enter the name you have defined by prefixing with
"="
5. Click and select the "The Ignore blank" and "In-cell drop down" check
boxes.
6. Click OK.

Note: You can modify the named range by either modifying directly in the
"Refer To" text box or Deleting the old named definition and add a same
definition name. You cannot directly over the name definition because the
Range definition in the Refer To text would still retain the same definition.

Challa Prabhu

Alun Jones said:
In VBScript or C#, I can rename an excel ListObject by simply writing a line
such as:

ListObject lo = (ListObject)ws.ListObjects["List1"];
lo.Name = "Certificate List";

Now, how do I rename a list using the interactive interface, without code?

Thanks,

Alun.
~~~~
Click to expand...
Click to expand...
 
Back
Top