Just so you know, OP, Border Collies have a bad reputation in a surprising amount of people's eyes. However, they are hands-down the cutest puppies and have lovely smiles!
I grew up with Border Collies (one failed farm dog, and one that we rehomed from a pet home, which turned out to be TERRIFIED of sheep and horrible to walk on a lead). I grew up and got two collies of my own.
On reading a book, it advised me to avoid the boisterous ones (too hard to train, headstrong, possible behavioural issues) and the quietest one (might be too shy to withstand the rigours of sheepdog training), so go for a middle one. I did, and she is wonderful. She is so relaxed that she is virtually horizontal, except when working (she is razor-focused and will not take "No" from cows or sheep. Indeed, they take her more seriously than me when it comes to being herded). She is eager to please and was ridiculously easy to train, but I was at home a lot at the time, so could spend a lot of time with her as a pup. She likes to relax at home, come to work with me, go for a drive and see the sights, pootle around for a walk, or plonk herself on the pavement outside the butcher's shop and switch on her "RSPCA Christmas Appeal eyes" until the butcher gives her bones... She's the kind of dog that you don't need on a lead, and doesn't care for resource guarding or any of the stereotypes of collies.
I didn't get as much choice in terms of boisterous/middle/quietest with the youngest. She was just the last female in a litter. She's bright, and learnt everything quickly. The only thing that I couldn't train out of her was needing to rush EVERYWHERE at top speed. I wouldn't have her off-lead anywhere near a road because of this, but the rest of the time, she doesn't go far from me. She's the dominant dog, and only really guards her favourite balls, except when I am required to throw them for her. She's only ever truly calm when working (silent but deadly) or when at home and there aren't distractions like cars passing, or the neighbour's dogs barking.
Yes, they both enjoy work (but only really go out to work in the spring and summer). They like to play ball and visit pet shops, where they are much admired for their good manners (and because most people take tiny dogs shopping, so big ones are a nice change). They like it when we are visited by family members that they adore (to the point of jumping the 4ft fence in excitement to get to them quicker...). They're happy to be petted by random passing children in the park, although the youngest is wary of fast-moving children that aren't used to dogs (e.g. babies with wildly flailing arms, or a child that was like a bull in a china shop). They also enjoy time relaxing at home, and are very fond of watching farming programmes (especially if sheep are featured) or One Man and His Dog...!
So... if you do go for a collie, try to meet the litter early and choose a middle/quiet one for a pet home! Spend as much time as you can with them when they're a pup to instill good values and manners. Just make sure that your family don't hype them up all of the time as a puppy, otherwise they'll think that is OK and what is expected of them. The right match will be an absolute joy for you and your family. I hope that it all goes well for you, whether you choose a collie or another breed.