KavazovAngel: And by the way, never get games from Steam if you want to mod them. Especially not games like Morrowind or Oblivion. ;)
Common misconception.
Steam has no problems with games that are actually meant to be modded, because those load separate files. The only potential problem is if a specific patch is required, and even then those are pretty rare.
As for games that are not actually "meant to" be modded, these can have problems because you are changing existing files. These are things like unofficial patches and mods that are closer to hacks (involve changing texture files, bootstrapping things, etc). But by turning off auto-update and not verifying cache integrity, you will have no real problems. This can be problematic for older games (where this type of modding was widespread), but older games already have problems on Steam. Pretty much all newer games either support modding, or actively make it so you can't mod.
But either way, the whole "don't use Steam if you want to use mods" is a very common misconception that just isn't true. Obviously there are exceptions, but there are exceptions to every rule.