This won't work anyway, since UserDir isn't set in the httpd config and can't be set from .htaccess (try it - you'll get an internal server error).
You might be able to get it to work with mod_rewrite - from the perspective of an HTTP request, there isn't anything special about the tilde. You can't just create a ~ directory, true (well you could if you wanted, but it wouldn't do what the OP wants to do), but that is pretty much irrelevant to this discussion.
I did some quick tests, and you could do something like this (in the domain's .htaccess file):
RewriteRule ^~([a-z])/(.)$ $1/$2
(a plus would be better after the [a-z] -- the forum somehow won't reproduce either a literal plus sign or +
(put a [R] at the end of the second line to redirect people to the new URL)
Take this with a grain of salt - I'm not a mod_rewrite expert, and you might need to adapt it a little for your needs.
That would take a request like http://example.com/~foo/bar.html and map it to example.com/foo/bar.html (you couldn't easily give FTP users access to a subdir of your main dir, but you could make a symlink to a subdir of their home dir, e.g., "ln -s /home/otheruser/public_html /home/youruser/example.com". Alternatively, you could redirect to http://foo.example.com/bar.html (with one subdomain per user).
Anyway, you might not be able to get it to work 100% the way you want, but I think it'll be possible to do some magic to get this to work. I'd personally suggest sending a redirect so that you can start the process of moving users away from the "~username" syntax.