Excel 2003 has the Data/Lists/Create Lists functionality. Creating
lists is no problem, but I'm at a loss as to renaming any lists I
create. Excel automatically names the lists in the file as 1, 2, 3...
It would be nice to be able to rename them as desired.

I've been using a workaround of adding a named range over the top of
the list.

example: ActiveWorkbook.Names.Add Name:="AddressList",
RefersToR1C1:= range

Obviously, this is clumsy. Is there a better way?

so how do I change

Set rng = ws.ListObjects(1).ListColumns(3).Range

to something like

Set rng = ws.ListObjects("AddressList").ListColumns
(3).Range


Art

With a list of your desired names in H1:H10

Sub Rename_Lists()
Dim lst As ListObject
Dim rng As Range
Dim i As Integer
On Error GoTo endit
Set rng = Range("H1")
For Each lst In ActiveSheet.ListObjects
lst.Name = rng.Offset(i, 0).Value
i = i + 1
Next lst
Exit Sub
endit:
MsgBox "there is a List by that name, re-type a name"
End Sub

To rename just a single List.

Sub rename_one_list()
ActiveSheet.ListObjects("List1").Name = "AddressList"
End Sub


Gord Dibben MS Excel MVP


On Fri, 6 Nov 2009 10:47:47 -0800 (PST), c1802362 wrote:

Excel 2003 has the Data/Lists/Create Lists functionality. Creating
lists is no problem, but I'm at a loss as to renaming any lists I
create. Excel automatically names the lists in the file as 1, 2, 3...
It would be nice to be able to rename them as desired.

I've been using a workaround of adding a named range over the top of
the list.

example: ActiveWorkbook.Names.Add Name:="AddressList",
RefersToR1C1:= range

Obviously, this is clumsy. Is there a better way?

so how do I change

Set rng = ws.ListObjects(1).ListColumns(3).Range

to something like

Set rng = ws.ListObjects("AddressList").ListColumns
(3).Range


Art

Thanks!

On Nov 6, 3:03*pm, Gord Dibben <gorddibbATshawDOTca wrote:
With a list of your desired names in H1:H10

Sub Rename_Lists()
Dim lst As ListObject
* Dim rng As Range
* Dim i As Integer
* On Error GoTo endit
* * * Set rng = Range("H1")
* * * For Each lst In ActiveSheet.ListObjects
* * * lst.Name = rng.Offset(i, 0).Value
* * * i = i + 1
* * * Next lst
* * * Exit Sub
endit:
* MsgBox "there is a List by that name, re-type a name"
End Sub

To rename just a single List.

Sub rename_one_list()
ActiveSheet.ListObjects("List1").Name = "AddressList"
End Sub

Gord Dibben *MS Excel MVP

You're welcome.

Gord

On Fri, 6 Nov 2009 13:29:55 -0800 (PST), c1802362 wrote:

Thanks!

On Nov 6, 3:03*pm, Gord Dibben <gorddibbATshawDOTca wrote:
With a list of your desired names in H1:H10

Sub Rename_Lists()
Dim lst As ListObject
* Dim rng As Range
* Dim i As Integer
* On Error GoTo endit
* * * Set rng = Range("H1")
* * * For Each lst In ActiveSheet.ListObjects
* * * lst.Name = rng.Offset(i, 0).Value
* * * i = i + 1
* * * Next lst
* * * Exit Sub
endit:
* MsgBox "there is a List by that name, re-type a name"
End Sub

To rename just a single List.

Sub rename_one_list()
ActiveSheet.ListObjects("List1").Name = "AddressList"
End Sub

Gord Dibben *MS Excel MVP