I really enjoyed the mods to Star Trek Bridge Commander. This modding community is active to this day. Some of the best mods made improve every aspect of the original. Just look at fileshack and see what's available: ships, music, sound effects, bridges, damage models, atmoshere (space/planets/stars/nebulas,etc), gameplay mechanisms. Age of Empires/Mythology also had some great user mods/maps. Any game with polish has free tools to create mods.