You can overclock your CPU (it's common to get around 3.6Ghz out of it with a good cooler) and add more RAM to bring that up to 8gb specifically for photoshop. As for gaming upgrading to a single 6870 or 6950 would be a good match. Finally, for application performance you may want to consider using an SSD as your primary, boot drive while using your current drive for storage. You can use a live CD like Knoppix to make an image of your current drive, store it on an external drive, and then put that image on the new SSD.
Your motherboard is fairly out-dated but will support upgrades.
First off id say there's no point doing anything to the RAM at all, DDR2 can be fairly expensive these days.
Your motherboard has a PCIe x16 slot so graphics card would be your best option to upgrade in terms of gaming power. Your 650w PSU is more than enough to support even the highest end of cards (GTX 580/ATI 6970).
Your motherboard has plenty of SATA connections which would enable to you go out and buy a new SATA II HDD (7200rpm) at the size of your choice. Recommended 1TB HDD's include: Samsung SpinPoint F3, SeaGate Barracuda, Western Digital Caviar Black.
To gain more processing power in applications, your only real option is to over-clock your CPU. If we ramped up the budget id be recommending a new AM3+ motherboard with DDR3 RAM support which for applications such as photoshop, would give you much better performance.
By the way I'm assuming you have a 64-bit OS. Running multiple CS programs at once (Photoshop, fireworks, Dreamweaver, and Flash for example) will have you wanting for more RAM if you only have 4GB. Heck my work PC with CS4 slows down if I have more than two of those three open or two of those open and firefox. No they won't spring for the extra RAM around here (cheap *&%). Get more RAM first and overclock your CPU.
I don't know what your RAM timings are but for $70 you can get that 4GB G.Skill kit ^_^. You have a 940 BE so not overclocking it is wasting it . Don't use the stock cooler though -_-. A reasonably priced cooler is this sunbeam