The Steam Workshop for Skyrim is totally awesome, but it also has caused me to examine why I don't generally don't get much of a kick out of mods, even now when the technical hassle has been removed.
The first (and smallest) issue that I have with mods is that, it's just, like, your opinion man. I mean hey, maybe you thought the texturing on that one rock in Whiterun needed "fixing" - but personally, I don't really like pixels so sharp I could poke an eye out on one. The graphical ones bug me the most because when it comes to visuals there are so many shades of grey that it's impossible to please, well, anyone really, and the gameplay stuff is in a similar but lesser situation. Mods allow you to actualize what you and maybe 10 or so other dudes want out of a game, and somehow that concept annoys and frustrates me to no end. The endless second guessing shows that they're all wrong in their own special way.
I'm maybe being hypocritical here however, because I've already found a couple mods that suit my needs. There are a couple mods where, yes, I'm one of those ten dudes, and I agree with your very specific take on this issue. And yet I can't understand or deny my irrational hate for these people who want to put like 30 trees in Whiterun for no discernible reason. Perhaps it stems from the times I've been burned by mods, buying their bullshit claims only to find they either break the game or make it look terrible. Some may come tantalizingly close to being a positive change, but I've yet to find one that approaches the work that's already been put into the game professionally, qualitatively speaking.
However, there's always the small stuff that's done to near perfection - a mod I truly couldn't argue with - and yet I find even then dissatisfying. I suspect that this is because there's always a niggling feeling that I'm moving further away from the game that it's meant to be when mods are added into the mixture. For example - so far the only mod on the Steam Workshop that interests me is the lighter dragon bones mod. Anyone who has played a decent amount of Skyrim will know how unnecessarily heavy the dragon bones and scales are, to the point where they can fill a good quarter of your inventory per haul. This is ridiculous, and I know it's ridiculous. I shouldn't have to throw 35 potions on the ground to be able to carry a couple items back to my home base in Whiterun. I know this is silly, and yet I can't help but feel that I'm cheating by using the fix for this issue. This is because I take a lot of comfort in the fact that I'm playing a game how it's meant to be played - that it's exactly the intended experience. This, I believe, is a part of the reason why so many would opt for consoles over PCs any day of the week - there's a lot of frustrating tinkering involved in making sure you get what you want out of a PC. So even if I'm fairly certain that a mod would be a categorical improvement on the game, I guess I just would rather trust a professional game designer to give me a good time rather than risk an amateur in the first place.
But I think my ultimate problem is simpler then the rest - it's the inconsistency that stops me from getting too enthused about modding. Aspects of a mod can be extremely well done, but there's always that one thing, that one letdown, that brings the seam between user-made and developer-made content into stark contrast. Hell, simply the act of installing a mod shows that you're aware that it's in the game, and no matter how tightly integrated that mod may be, I tend to scrutinize it to the nth degree simply because I know it's there, and I know it's non-essential.
I guess that all together shows why I'm so curmudgeonly about what is actually a wonderful community effort. All this negative Nancy-ing combines to make modding a rather unpleasant experience for me, even now when it's been removed from the hell that is filefront and very messy game folders. I'm curious to know if anyone feels similarly about it, or at least sympathizes with some aspects of what I've said here. Or, y'know, come from the other side of things and tell me why I'm looking at this the wrong way!