Seeing the success of Counter-Strike, and the mod-mania of that era, got me interested in modding, where I was first exposed to the complexities (and math!) that "real" software entailed.
From there I went on to write some standalone, small games, then went deeper down the C++ rabbit hole which helped me get my first software internship. The rest is, as they say, history.
Are there still active modscenes for newer games? Minecraft is the only one that really comes to mind.
I suspect there's another problem with mods today: asset production. In Half-Life's day even I could hack out a semi-passable player model in a day or two. Nowadays, producing assets for games has been made dramatically more complex. In short, you really can't find a lot of people willing to produce that quality and complexity of art for free. The size of teams required to put anything together, as a result of this, has also put a lot of things beyond a mod team's modest reaches.
I think that's why a lot of the indie excitement is around casual and "low-fi" games like Minecraft. Assets have always been a major blocker, and when you embrace the fact that you're not AAA, you have a better experience with it.
I haven't really checked moddb.com since Unreal Tournament 2004 was the big online game, but it seems to be going strong.