I enjoy the more professionally made mods for online games, such as SourceForts for Half Life 2: Deathmatch.
However I have found little interesting single player mods for any game, mainly because I can't be bothered to download and install them a lot of the time. I get especially annoyed when my friend tells me to get Oblivion and install some mod which apparently makes the game worth playing, since my views on the Elder Scrolls series are remarkably similar to the comments Yahtzee made on his review of Oblivion and because I feel that any game which needs modding to be good is pointless. At least Unreal Tournament and Half Life are great games on their own.
Mods in themselves are only useful to those who already have a game anyway, and it makes it hard for me to shake the feeling that these people could have put their time and effort to making a full version of a game for everyone to play. There's also the fact that the only good mods I've seen are ones for game which are already awesome, so having mods for bad or mediocre games is pointless.
Away from my ramblings and more to the point of the thread though, I rarely use mods for games, which makes my choice in the poll very difficult since there is no "Sometimes" option.