My children were 6, 4, and 2 and all of them enjoyed The Haunted Mansion. I thought my 4 year old would not want to go on it because she is definitely not the adventurous type. My husband was going to stay with the two little ones while I took our oldest. However, when we approached the ride, she begged to go. I asked if she was going to be scared and she said "No!" So I asked the CM at the entrance how scary it was and he said, "It's kind of like 'Scooby Doo' scary." Well, that helped put things into perspective for me. It was very similiar to Scooby Doo in that it was "funny" scary, not "scary" scary. If that makes any sense to you. So, we went and for the longest time, that was her most proud memory of WDW. She was so proud of herself for doing something "super scary!"
Now that she's 7, she doesn't remember it and she questions whether she'll want to go on it again. I guess you can chalk it up to more life experience and a little more common sense now that she's older. She did see the HM movie and liked it, so I'm sure she'll want to go on it when the time comes.