Will it make you scared? Absolutely. We went last year, and I did my best to keep up the "brave face", but was quite scared several times.
Will it ruin your vacation mood? I doubt it. The next day, I was telling people about it and realized I was telling "fun scary" stories, not "terrible scary" stories. I was considering going again this year, and if it weren't for financial considerations, I would do it again.
If you do go, don't miss the Bill and Ted show. It is completely worth your time, and possibly one of the funniest things I have ever seen.
One thing to consider... it's a late-night event, and to see most of it -- I don't know if you can see it all in one night due to the crowds -- you will be out very late. If you are going to feel like you missed out on something at Disney because you weren't able to be up early in the morning bright-eyed and bushy-tailed, it probably isn't a good investment of your time.