Update: AAAGHH! Just saw that Gigantus put up a similar guide a few days ago...oh well. Now there are two gudies! Mine does it the old fashioned way, and no downloads required, so this one is better 
This topic has caused many, including me, a good deal of headaches over the years. You download a wonderful mod...only that it won't run with Steam. The MTWII game pops up, not the mod. A long time ago I found a guide that was...well...not that good. I looked for it again recently and couldn't find it. So I will make a new guide, as people are complaining about this and asking questions all the time!
If the mod folder is in your MTWII directory and it won't appear as a selection, you are experiencing this problem. You see, when you click "play" in the MTWII launcher (after you click "play" in Steam menu), if there is more than one game configuration, a drop down box should appear. Vanilla MedII should be at top, with mods below. The thing is, you need to tell the game about the mod.
This is harder than it sounds. This guide will explain how.
To start, go to your "Run" command. To do so, click on the menu icon in bottom-left of the screen. it should be on the right side of the menu that pops up. If it is not, search your computer's help for "Run Command". Follow those instructions to make it visible.
I will be using Stainless Steel 4.1 (I don't have Kingdoms, so can only do up to 4.1) as an example (though this is not an endorsement of the mod
).
Launch the run command and enter "Regedit" (without quotes).
You might need to confirm the action as administrator. Do so. A window will pop up, with a directory on the left side. Go to the following path:
Computer/HKEY_LOCAL_MACHINE/SOFTWARE/Wow6432Node/SEGA/Medieval Two Total War/Mods/Unofficial
There, you are at the unofficial mods folder! Now right click under "Unofficial", then go to "New" and "Key". Name it after your mod, though the name doesn't really matter.
The contents of the folder will be blank except for one entry, and your screen should look something like this:
Three "strings" are critical for the mod to work. To add a new string, do right click New->String Value.
When you are done naming it, hit enter twice to go to the value entering part.
1) Name the string "ConfigFile" (without quotes). Go into your MedII directory, and look for a file called "Name-of-the-mod.cfg". If it is in the mod folder, drag it out into the main directory of MedII. Now enter the name of that file (including the .cfg at the end) in the "Value Data" box. Press ok.
2) Name the next string "Path" (without quotes...you understand by now, right?). Go into the MedII directory and find the mod folder. Now just name the path from the MedII directory to the Data folder of the mod. It might only be "Cool_Awesome_Mod", as the data folder is often directly inside the mod folder. Enter that path previously described in Value Data.
3) Finally, name the next string "GameExe". This is pretty easy. If it is MedII, just put "medieval2.exe". If it is Kingdoms, well...put kingdoms.exe.
There, it should work now! For aesthetics, you can add an extra setting (you should)
*Named "DisplayName". Will be what is displayed in the dropdown box.
There are others, like "Author", "FullName", and "Language" that have no effect on really anything. Your mod key should look something like this:
Now exit regedit (you don't even need to save), go to Steam, and start MedII. Click the play box on the window that comes up. The game should not start, and instead the mythical dropdown box should appear, showing "Medieval II Total War" and "Your-Mod". Just click on the mod name to play that. If you have problems with startup, it could be for a number of reasons. Make sure that all the string names are entered properly.
Enjoy! And please press the wonderful green button below my avatar...