I was eight years old when Neil Armstrong stepped off the LEM onto the Moon. It was an overcast day but the thing I remember vividly was how quiet the city was; aside from a few trucks in the distance and the wind blowing between the buildings there was simply nothing to be heard. The street was utterly deserted, more deserted than it would have been in the middle of the night. I'd gone out to find someone to play with, but gave it up for a bad job. I came in just in time to watch Armstrong step off the LEM. Cronkite couldn't make out what Armstrong said -- later it turned out Armstrong had bungled his line.
The only thing since then that has come close for shared amazement was 9/11.
The thing is there will never be another moment like that, not for manned space exploration. For those of us too young to remember WW2, the Apollo program was the biggest, most exciting thing that had happened in our lifetime. Older people had grown up with the Moon as the very symbol of something that was impossible to obtain. Every human being who'd ever lived and who wasn't blind had looked up in the sky and seen that big fat Moon hanging up there looking so close you could touch it.
Mars isn't like that. For most people it's just a name. More people have seen fake Mars in movies than have seen the real thing. So I'm guessing that few people will interrupt their lives to watch the first step on Mars. Maybe some of us will, but there won't be the same amazement, that sense of witnessing a once-in-a-species event.
Speaking of movies, one of the things that happened after 1970 is that production values on sci-fi movies went way, way up. Most people today have grown up watching representations of humans traveling to the stars; that's the new milestone for the human imagination. So I don't think there will ever be the kind of adulation for real astronauts that we had in the 60s. Actors are more photogenic than real astronauts and they don't spend their time doing tedious and inexplicable things.
But I don't think it's impossible to get people interested in space exploration; only that it's folly to put men up there and expect the public to automatically get excited. Henceforth space exploration is only going to matter to people who've been educated enough to find science interesting. That in itself is a worthwhile goal.