I've never made any mods for Morrowind or Oblivion, though I've messed around in the Construction Set for Oblivion a few times. However, I have a lot of big plans to make various quest and adventure mods for Skyrim, assuming I'm able to figure out the Construction Set.
I played A TON of Oblivion mods, and in doing so, I learned a lot. I learned what works and what doesn't, and clearly saw what people did right and what people did horribly, horribly wrong. With this knowledge, I think I'll be able to make some really fun quest mods once Skyrim comes around, so I'm pretty excited. I've already made several notes about the story and key features in the quest mods I'm planning without making too big of assumptions about how Skyrim's engine will work.
What about you guys? Modding is a huge part of the Elder Scrolls experience for me. Any of you guys on consoles out there should really invest in a gaming-capable PC just for the hours and hours of added gameplay from player-made mods.