It really depends on your tolerance for monotony. Borderlands is easily a game I'll keep until my 360 becomes aged and ragged and is no longer a decent platform for gaming, just because it's entertaining. Who REALLY needs story in a shooter? All you need to know is that you're killing shit, and that should be a good enough reason to play it. Co-op is pretty good, but it's not like anything was tailored exclusively to multiplayer. You get better loot and enemies become more powerful with more people. After the first playthrough, the difficulty is ramped up a bit and it's tough to play solo since you'll get mobbed by a few things at a time and they'll easily be able to overpower you.
It's really one of those "play it to believe it" sort of things, and the novelty of it all is tough to put into words. Rent it or something before you buy into everyone's harsh judgment, since it seems like I'm one of the few people who wasn't absolutely disgusted by it.