I think it'll probably take a 7800GT or ATI 1800something to get a really noticeable improvement from your current setup.
If you have $500 to spend, consider a new mobo and 7800GT or GTX. The problem you'll still have though is the 754 cpu. It's tough to go anywhere from a 3200 if you're stuck with socket 754. You can get 3800s in socket 939 fairly cheap I think, but then you're talking $800 and a near-complete system rebuild.
I think I'd recommend sticking with what you have for now... An nvidia 6800 Ultra might give a little speedup as would a faster cpu, but you won't get anywhere going to pci-e unless you also jump to the next generation of vid cards, and at that point it would be crazy to buy a new mobo and cpu and stick with socket 754 cpu. You'd never be able to go dual core, lose out on dual channel memory, etc.
This is why I always thought socket 754 was a bad idea... No way to incrementally upgrade. If you want to upgrade one thing, you toss out the whole mess.
That said, for $800 you should be able to get a nice socket 939 mobo, cpu, and 7800GT. 2 gig memory (2 1 gig sticks) would be a nice bonus if you can fit it within your budget and BF2 definately wants 2 gig ram, but that's gravy right now if you're not into BF2.