I just figured out why I'm having this problem. Perhaps some of you have the same cause?
I was able to find a workaround, but I don't have a permanent fix.
Cause: Steam/Civ5 was installed with the Administrator account. I have a standard user account with UAC enabled. Even though the game is launched from within my account, some settings are pulled from the Admin account and some are pulled from mine. It's poor coding in the game for sure but perhaps not in Steam.
Solution:
1. Log on to Windows using the account with which Steam was installed. (Note: If you were logged on with your user account and asked for an Administrator password when you installed Steam then Steam probably installed as the Administrator account. As in my case.)
2. Launch Civ5 and install the Mods as normal
3. Exit Civ5
4. Copy this user's "~\My Games\Sid Meier`s Civilization 5\MODS" folder to "C:\Users\Public\Public Documents" (You cannot access the Administrator's folders from your account)
4. Log off from this account
5. Log on with your account
6. Copy the files you need from the "C:\Users\Public\Public Documents\MODS" folder to your "~\My Games\Sid Meier`s Civilization 5\MODS" folder
7. Launch Civ5 and install the Mod
If anyone knows of a permanent fix, please, let me know. Although this workaround fixes my issue, it's a pain in the butt to do every time I want to try out a new mod. That said, I hope this workaround helps out someone.