The Mods button is disabled in the MainMenu.lua file. To re-enable this button you will need to navigate to the MainMenu.lua file, open it with a text editor (like TextWrangler) and comment out line 33.
~/Library/Application Support/Steam/SteamApps/common/sid meier's civilization v/assets/UI/FrontEnd/MainMenu.lua
Assuming you use Steam
The line is:
Controls.ModsButton:SetHide( true );
To comment it out simply add two hyphens like this:
-- Controls.ModsButton:SetHide( true );
This should re-enable the Mods button on the Main Menu.
Some mods work and some do not, it seems rather hit or miss. If you have a xxxx.Civ5Mod file use something like Ez7z to expand the file which will create a folder that you can then put in your MODS folder which should be similar to this: (Depending on which version of Civil you have)
~/Library/Application Support/Sid Meier's Civilization 5/MODS/
or ~/Documents/Aspyr/Sid Meier's Civilization 5/MODS/
depending on whether you have changed the setting to use the Library instead of Documents.
NOTE: If you are running OS 10.7 (Lion) then Users/<your_name>/Library/
is hidden in Finder. Hold the Alt key down and select the Finder's Go menu. You will see Library as one of the options in that menu.
NOTE 2: Currently, every time you download a new patch for the game it overwrites your MainMenu.lua back to default so you will have to comment out line 33 again.
Best Answer
Two good options I use:
Go to the Steam Workshop site for Civilization V
As of version 1.0.1.674, Civilization V's Steam Workshop contains a list of mods with popularity and rating details, indexed by category and searchable with free-text. Mod developers don't have to publish their mods there, a mod is just a file you can download, but it seems most developers do indeed publish on that mod hub.
Go to the Civilization Fanatics Center
The popular civfanatics website has a download section with many mods, maps and more; you can also browse its forums directly. Mod developers typically publish a lot of details in these forum threads and you can also read what other people think about the mods, so I recommend browsing that forum if you're looking for something new and interesting.