[quote name=\'CJBojangles\' post=\'238233\' date=\'Mar 25 2010, 02:17 PM\']Have the HTC Hero now, don't think there's any way to create custom text-message sounds, though.[/quote]
Sure there is. The Messaging app lets you set a custom notification sound specific to text messages, and if you have Rings Extended installed (and you should), you're no longer limited to the pre-canned list of sounds that they give you or what you load manually into I-think-it's-Media\Notifications on the SD card.
(I also dumped the crappy stock Messaging app (on a Samsung Moment that uses stock Android; I don't know if you get a different app with Sense UI) for Handcent SMS a couple of weeks ago, and haven't looked back. Among other things, it'll let you set a custom sound for each individual contact if you want, so you can fire the TPiR lose horns when the mother-in-law texts.)