It depends.
No really it does.
Certain games work well with user-created content.
Others not so much.
Take for example, Oblivion.
Great game, though dialouge is not the best ((Namely having guards insult you on hte street, then when you talk to them, they like you...Bizarre.))
However, there is a slew of user made mods, ranging from new quests, areas, OP weapons and Armor, etc.
I don't need to have those mods to enhance my gameplay, but it is fun to try them out. Except the nude mods, oddly enough it is hard enough to tell people that you aren't a weirdo for playing an RPG like Oblivion, but when they watch you play (and you forgot to turn off the nude mod), they really, really, I mean really get the wrong idea. Ranging from...nevermind.
But then again you have the games that rely heavily on the player-base to make the maps and content, which isn't the best idea as states above, 99% of the world sucks at map making.
Plus it seems lazy.
I would like more games that are easy to mod, but I don't want them to RELY on the user for the content. Odds are, it's gonna suck.