I was pretty dead set against Steam when I heard that Civ 5 will require it, but I'm starting to loosen up a bit. I just don't like the idea of some third party being involved with my games, nor do I like having to activate anything online. I still haven't decided yet whether I'll go for it or not though. I'll probably wait awhile and see how things are working out for everyone first. At least with two games I want to play requiring it, Steam won't just be some bloatware filling up my hard drive and using up resources, which was one of my initial reactions to Civ 5.
Seriously, it is impossible to be anti-Steam unless you've never played any Valve games or bought anything on it. Try TF2/L4D with a couple of friends and you'll soon see how spectacularly useful it is for organising groups, patching, handling chat and so on.
As proof of how good Steam is, my DVD drive broke over year ago now, and the only reason I'm considering fixing it is Steam don't sell the Limited Edition NV. Everything else I've bought for a year has been off Steam, cheaper than retail, and often in a package with vastly more games (e.g. entire of THQ's catalogue for the past few years along with the Dawn of War 2 expansion, for the same price as the expansion alone in shops).
E: Actually you can probably just activate the CD key on Steam and download, I don't think I'd even need the disk.
E: No wait, I bought C&C4 having forgotten my drive was broken. Having to go to the length of borrowing someone's spare external to play it made it seem even more disappointing.