Apache must be installed with mod_userdir:
Look for the following in httpd.conf:
LoadModule userdir_module libexec/mod_userdir.so
AddModule mod_userdir.c
And
# UserDir: The name of the directory which is appended onto a user's home
# directory if a ~user request is received.
#
<IfModule mod_userdir.c>
UserDir public_html
</IfModule>
If these are not present, try to add them in their respective areas (look for LoadModule and Addmodule) and place the <ifModule directive down below.
If you already have these in your httpd.conf, then you need to look for something like:
<Directory /home/*/public_html>
and see if it is somehow configured to disallow web viewing. You can even try to comment out that whole section with "#"s.
And make sure you are setting up your users with a "public_html" directory inside their user directory: (/home/username/public_html).
I don't know of a quick way to get rid of the tilde, but it should be possible with mod_rewrite.