LinkBack URL
About LinkBacks
Can an excel List (2003 feature) have a Name? How to distinguish between two different lists from the same worksheet in VBA?
Is there somewhere a tutorial on List manipulation in VBA?
Can an excel List (2003 feature) have a Name? How to distinguish between two different lists from the same worksheet in VBA?
Is there somewhere a tutorial on List manipulation in VBA?
Hi,Originally Posted by rvExcelNewTip
apparently they do, as List1 List2 List3 etc.
To discover, Record a Macro, and setup a list, select an option from the list.
Setup a second list and select an option from that list, then return to the first list and select an option.
Perhaps you could also define a Named range, and make a list from that to check the Named range is ignored by the List function.
hth
---
note, the first thread in the Miscellaneous Forum has many links
---
Si fractum non sit, noli id reficere.
It would be worth just checking what sort of list you mean. I have assumed CustomList, if so the application. ... addcustomlist, customlistcount and getcustomlistcontents are the usual tools. What do you want to do?
Using the recorder, I discovered that you can indeed supply your own meaningfull name to the list. The Add method of the ListObjects collection , used to create the list does support Named Ranges:
Worksheet("One").ListObjects.Add(xlSrcRange,Range("MyListRange"),,xlNo).Name="MyThirdList"
----
I don't know how this sort of lists is "officially" called. But it is the list that you create by selecting Data -> List -> Create List. It has nothing to do with CustomLists.
I want to use it for generating a self adjusting chart. I'm aware of the "usual" techniques based on Named Ranges and/or Count functions
-----
The next challenge I have is how to manipulate that kind of list through coding e.g. updating the contents of one item in column 2, based on the value in column one. Therefore I have to address somewhere the individual items in the list (maybe by reverting the list to the underlying range?).
Hi,
Yes, I assumed the Data, List,
from your macro during the creation you perhaps noted the Range, asin
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$D$6:$D$12"), , xlYes).Name = "List1"
for your next purpose you might be able to Dim a rng, and set this to the Range of "List1", and use 'For Each c in rng' (untested as at 4:35am)
hth
---
You might find this code gives some pointers
Sub a()
Dim li As ListObject
Dim i As Integer
Stop
Set li = ActiveSheet.ListObjects(1) 'just get the first list
li.Name = "ListX" 'give it my own name
Set li = ActiveSheet.ListObjects("ListX") 'instead of using the index you can use the name
i = 2
li.ListRows.Add Position:=i 'add a new row at line i
li.DataBodyRange.Resize(1, 1).Offset(i - 1) = "new" 'populate new row
End Sub
The variable for List object is LI in lowercase - not one of my best choices.
Anyway the code above should give you an indicator as to some of the main attributes of a listobject.
Note that there is an InsertRange but this appears to be active only when the list is selected.
hope this helps
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks