Valve and Bethesda made numerous mistakes with this implementation, but I still consider it a good idea. I'm definitely planning to allow paid mods in my own games, if I ever get one ready for retail. But here's where they went wrong.
1) They set a minimum price far too high. Relatively few mods are worth a dollar, even the ones that are worth buying at all. Give supply and demand a free hand to set prices, and I think most average-sized mods would have been priced around $0.20. Some might have been able to sell at a much higher rate, but not many.
2) They didn't protect from fraud. As soon as the announcement hit, people started uploading mods they didn't make - there was already a massive corpus of free mods, after all, and basically no protection against this. The least they could have done is give a decent warning period, for mod authors to decide whether to start selling their mods or not, and to search for fake versions being uploaded without their consent. They didn't do that, and they definitely didn't do any sort of technical measures, like comparing uploaded mods' checksums against those already uploaded. All of that is easily foreseeable because I actually foresaw it - I've been planning how to do this in my own games, and all of that was on my list before they even announced their feature.
3) They didn't share the profit well. Valve was taking a 30% cut, which is already more than they do for full games, and then Zenimax was taking another 40%. I can see that, because the base game does a non-trivial amount of work for the mod, that they do deserve some compensation (although I'd say increased sales are the true payment to the publisher). But a cumulative 70% is just ridiculous. I'd argue that no less than 50% should go to the modder. For my own games with paid mods, I'm thinking more in the 75:25 or 90:10 range, or even 100% to the modder (because, after all, a vibrant modding community brings about more sales, so the marginal loss on hosting is more than recovered).
4) They launched it suddenly, with no notice. Nobody had any inkling it was coming, least of all the modders who would be most affected by it. Valve and Zenimax should have given at least the big-name modders some heads-up, so they could think and have time to rationally decide whether to start selling, and for how much, and to work out any licensing issues in multi-person teams. And perhaps if gamers had been able to see it coming, they could have realized it was a good thing, instead of letting the knee-jerk reaction control the debate.
They did, however, do one thing surprisingly right, which deserves recognition: they gave full, automatic refunds within 24 hours of purchase on any mod you didn't like. That's definitely something necessary, and something very surprising to see from Valve.
Hopefully they can sort out these issues with the next game they try this on, instead of giving up on what is an excellent idea.