Afghan Cavalry
I'm kind of surprised no one has mentioned the cavalry units of Afghanistan, who fought both the USSR, and fought each other in the civil war with the Taliban.
These forces operated much in the way of traditional cavalry, including cavalry charges on riflemen and vehicles, and clashes between cavalry units. And, most interestingly, they were very successful.
Terrain
Afghanistan has some of the most extreme altitudes of any country, great highs and lows. Many hills, gulleys, valleys and canyons, with extremely narrow trails up the side of mountains. In this terrain, much of the land is inaccessible by tank, or even truck. Even Helicopters have many problems with the extreme altitudes, with some humorous anecdotes I better not get into.
In this environment, horses are the best method of travel in many mountainous regions. So, understandably, people fight on horses.
More surprising, is that these horsemen deployed and fought in the areas where tanks and other vehicles could be and were deployed by the USSR, Taliban, and other forces.
Method of Battle
From what accounts I've gathered, the main method of fighting was ambush, charging, and some dragoon tactics.
The cavalrymen all used rifles, though many also used RPGs against the tanks and infantry. The RPGs were critical for successfully defeating vehicles, of course. Knives, bayonets, and I think even swords also saw use, with accounts of men running down infantry then dismounting to finish them off with a knife.
Ambushing the USSR
When ambushing Russian convoys, the Mujahideen cavalry are reported by Russian sources to have never charged more than 200 yards, usually attacking from two or more directions. They hit the fighting vehicles and troop carriers with grenades and RPGs at close range, such that it's hard to effectively use heavy weapons. If infantry deploy, the horsemen run amongst the infantry, running them down and shooting/stabbing them, increasing the confusion and their defence against heavy weapons. Normally, these ambushes were paired with MGs waiting at the line of retreat, ready to mow down people fleeing the ambush.
Sometimes, they'd wait for reinforcements, and ambush them too.
Taliban War
The Taliban war involved some more haphazard tactics, that at times could be called incompetent or even silly. There are reports of soldiers charging long distances (some sources say a mile, but this is in doubt) over hilly terrain into Taliban bunkers. The horses would often form two lines, one behind the other, and charge from the closest available point.
Accounts talk of defending soldiers trying to shoot an RPG at the crest of a hill, in anticipation of the cavalry coming over the crest. Apparently, despite this and heavy fire from defending forces, the cavalry normally closed with the enemy troops, who often just ran at the sight.
With good luck, the cavalry mopped up the infantry with little resistance. Losses were high, but enemy losses were higher. In other cases, the cavalry was repulsed and or bloodied.
The hilly terrain was used to get close while exposing yourself for a minimal amount of time. So, a "mile" over hilly terrain would be comparable to a few hundred yards of flat plains.
At the last hill, the first line of cavalry would sometimes dismount, and begin firing at the enemy from cover. The second line of cavalry would pass the first, charging into the infantry and firing their rifles as they did so. The first line would soon remount their horses, and join in the charge.
Vehicles and MGs were sometimes involved in these encounters, but apparently failed to blunt the charge on many occasions. One report spoke of a re-purposed self propelled anti-air gun, which managed to flank a cavalry force while it was in a melee with the defenders. This vehicle fired on the cavalry and its own men, and drove the attackers off with many casualties.
Conclusion
It is possible to use horses effectively for moving troops, and combating the enemy. The more fantastic encounters seem to rely on incompetence, but even competent forces can be defeated by a cleverly utilized cavalry force (consistently).
This doesn't mean that major militaries should start adding horse cavalry units back to their armies. It does mean opportunities and circumstances can make them valuable to a war effort, even in the fighting. These are most prominent in Afghanistan, but these concepts and reasons are not unique to Afghanistan, even if extreme in its example.
That's what I know on the subject, about horses being used in the modern day. I'll try to dig up some citations.