Some advice on "lists" [table]

[Incognitus]

Hey,

Looking for some advice:

I want to start storing some sets of data using the List feature of 2003, however it seems to have a few holes that would make it unfit for my needs.

Firstly, conditional formatting doesn't appear to copy over on to new cells. Is this something that can be easily fixed?

Also, I want to include a few events on individual cell clicks - pop ups, a few functions that will calculate values, add dates that sort of thing. Is it feasible to continue to use these, and if so, what's the best way to about changing the code to accommodate the new formatting?

Thanks for any help guys.

[p45cal]

Playing around with Lists in Excel 2003 I find that conditional formatting does get copied down as you manually add new rows on the bottom row, however, copying over doesn't extend the conditional format unless you copy over just a single row and you paste-special|Values. I don't know how to get round that.

Regarding events and cell clicks, the ListObject has various ranges associated with it (Databodyrange, HeaderRowRange,InsertRowRange, Listrows, ListColumns etc. etc.)which are all dynamic. You can see these ranges in the Locals pane of the vbe if you step through a macro such as:
Sub blah()
Set xx = activecell.listobject
End Sub

Examine xx and you'll see that it even has a name you can assign it, so your event code can contain the likes of
If not intersect(target,listobjects("List1").listcolumns("Header Two").range)is nothing then…

[Incognitus]

Ah - so if I replaced things such as

selection.filter or whatever the deuce it is with databodyrange.filter then it would still work when I switch a range into a list?

It's a shame about the conditional formatting - mine still doesn't seem to work in the manner described - and copy/pasting large amounts of information is what I really need to use it for, but I suppose I can do things programically now!

I'll give it a whirl tomorrow.

Thanks very much for the help!

[p45cal]

Ah - so if I replaced things such as

selection.filter or whatever the deuce it is with databodyrange.filter then it would still work when I switch a range into a list?Yes I think so. For example, you could set your databody range in any number of ways, here are 4:
Set xx = Selection.ListObject.DataBodyRange
Set xx = Range("A1").ListObject.DataBodyRange
Set xx = ActiveSheet.ListObjects(1).DataBodyRange
Set xx = Sheets("Sheet1").ListObjects("List1").DataBodyRange
then you canm go about doing things with it:
With xx
.AutoFilter Field:=3, Criteria1:="4"
.AutoFilter Field:=3
.AutoFilter Field:=2, Criteria1:="555"
End Withbut you should check by experimenting yourself.