I really don't see how you can be sure of that. Who would want to live in a steel cave for their entire life? Anything we need from Mars could be extracted with automated machinery.
He is not popular. I am sort of reminded of folks from hollywood or tech who collide with politics... and not everybody becomes ronald reagan.
Also, someone doesn't get to be the richest man without controversy.
But controversial or not, I think he has been a net positive.
Would we so profoundly have electric cars if tesla hadn't shaken the status quo? Sure, now ford has cancelled the lightning, but they now have people who know about EV technology deep within their stodgy ICE stronghold.
Right now there are 14,000+ satellites in earth orbit, 11,000+ are from the US, and 10,000+ of them are from spacex.
By any objective measure, he has figured stuff out. And he has shaken things up.
Like how he figured out how to promise full self driving cars in about six months? For eight years running?
Like how he figured out how to cut USAID programs that prevented and treated malaria and tuberculosis with no plan or warning?
There is never going to be a long-term colony on Mars. It is the most childish conceit imaginable.