Ooh yes, they're great! I've recently got an Intuos 4, moving on from the Graphire 2 I've had for quite a few years now. I particularly hate using Photoshop without it, though I switch to it for anything remotely mouse-intensive.
Like SgtMac66, I've been doing quite a bit of video post work recently, mainly grading and adding shiny bits in After Effects. I'd originally assumed that tablets were commonly used in that world too, but none of the people I've spoken to seem to have one. Some popular plugins like Magic Bullet Looks and Colorista don't work properly with a tablet, which really surprised me.
One warning / piece of advice I would give is don't try an Intuos tablet unless you're prepared to buy one. Because, much as the smaller tablets are great, you'll want to upgrade...