I frequently ride public transit in Chapel Hill and Carrboro. It is the largest fare free system in the US. It is used by many people but growth in usage depends on many factors. Park and Ride lots make a big difference for people who live outside of town and must drive to get even close to their destination. Sidewalks make a difference because people who live close enough to walk to a bus stop have to have a safe place to walk. The Chapel Hill buses have bike carriers on the front so that bike riders can take the bus for part of their trip.
Chapel Hill is also infamous for the number of people who commute into Chapel Hill (and even Orange County more general) from places like Durham, Mebane, even Raleigh. I don't know numbers, but I would assume--especially given the traffic on 15-501--that the vast and overwhelming majority are car drivers. One of my coworkers commutes to Durham every day from Carrboro. The trip takes around 20-25 minutes. She takes the bus sometimes, but my recollection is that it takes about 1.5 hours.
Light rail seems highly unlikely to substantially change any of the numbers.
Chapel Hill/Carrboro also has the big advantage of being a geographically compact area (though located in an ever expanding metro area), and a highly affluent population that is--compared to surrounding areas--very racially and economically homogenous. It strikes me that many areas around the country that have high rates of public transportation (and bicycling rates) meet similar criteria.
But one of the biggest factor is how easy it is to find parking. Cities use a huge amount of their space just to store cars during the day. The more expensive and hard to find parking becomes, the more people will use free public transit.
That's the balancing act. It really is purely a calculus of time and effort. I can drive to work work in 14 min (std dev ~1 min--roughly!). I can park in my driveway and immediately behind my office building. Public transportation is never going to be able to compete with that in time or convenience.
And all of this takes time. People have to adjust to the new reality of bus transportation being easier and cheaper than owning and driving a car. Over time, people will make decisions about where to live based in part on the presence of public transit. And if businesses also locate in areas served by transit, then it's easier for people to live and work on a transit line.
It seems to me that the "new reality" you're talking about is really called "urbanization" whereas in the Piedmont we have "suburbanization" with pockets of denser development surrounded by sprawl. I don't remember where I read this, but a list of the worst examples of suburban sprawl nationwide featured The Triad, The Triangle, and the Charlotte Metro area as three of the worst. Tons of awful and non-sustainable development is going in all around Chapel Hill, Durham, and the Triangle as a whole. I think it's going to get a whole lot worse before it gets better!