OK, while normally I would not recommend a $300 AGP card for a rig like yours, one key point people seem to be missing is your native 1280x1024 resolution. To run the latest games at that high a resolution you are going to want a more powerful card like the 7800GS or X850XT.
BUT, and a big but at that, is the fact in many games you will not be able to turn up the detail levels anywhere near max on a P4 2.4GHz CPU. Because of your CPU alone you will have to dumb down details(not resolution) some, so the perfect match would be a card that is as cheap as possible that can handle 12x10 at whatever details your cpu can handle. A higher end card wouldn't hurt as say your cpu requires you to set medium settings in a game. Even if the X800GTO or 6800GS could handle 12x10 medium, the 7800GS/X850XT would have power to spare. Now the CPU may keep higher detils from being an option, but this reserve power the higher end cards may have at those settings could allow enabling GPU intensive eye candy like FSAA to be turned on.
Anyway, just something to think about. It's impossible that the same card would be that perfect match in every game so I'd side with a better card so you don't have to reduce details too low or drop out of native resolution. But a P4 2.4 is definatley not fully up to the task in many of the latest games.
For example, way back I did weeks worth of cpu scaling tests of the old NFS Underground 2 with a mobile XP2500+ from 800MHz up to 2.6GHz, and at 5 resolutions, low /med/high/max details, and on a few different video cards. It showed that a Barton 2500+(1.83GHz) was about the cut-off to where you could no longer run max details in that game even with a X800XTpe. On a slower cpu, It didn't matter what the resolution was, 640x480 max details on a slower cpu scored the same as 1280x1024 max details; showing it was totally cpu bound. Now that's an older game even, so no doubt the minimum cpu needed to handle max details would be even higher in todays games.