In 1972 Apollo 17 astronaut Captain Eugene Cernan became the last man on the moon. Cernan, a US Navy fighter pilot handpicked by Nasa in 1966 despite not applying for the space programme, nor having gone to test pilot school, went on to fly three space missions. He is the only person to have descended to the moon in a lunar module twice and holds the world record for the highest speed attained by a manned vehicle after the crew of Apollo 10 reached 24,791 mph during re-entry.

Cernan's story is told by director Mark Craig in Last Man on the Moon, a documentary screening at this year's Sheffield Doc/Fest. Craig shows the Apollo astronauts – now in their 80s – recalling an era when America had a presidential mandate to be daring, a license to venture into the unknown.

"When Kennedy challenged us to go to the moon we didn't know beans about it," says Cernan, now aged 80. "We had 16 minutes [of space experience]. Al Shepard [the first American to go into space] went up and came down. I was just a young lieutenant flying out in the West Pacific off aircraft carriers, and at that time I believed – and I think most other people did too – that they were asking us to do something that was impossible. And then all of a sudden we got involved – all of us. And the rest is history. Don't tell me I can't do it: I think that's the America I grew up in."

It's this faith in imagination that Cernan suggests the US space programme lacks today. Nasa has seen its funding decline over the decades since he left the moon. It now receives less than half a percent of the total federal budget.

"It's unfortunate – a half century ago Americans were walking on the moon," says Cernan. "Today we've been told it's going to take a trampoline to get us back to our own space station. That hurts quite frankly."

There's a tangible sadness to the film. We're shown Cernan walking around an abandoned launch site. Grass is growing through the concrete and rust is creeping up the tower. The hope of an era was shot into the sky and allowed to dwindle.

"I wonder how [Americans] feel about the film?," says Mark Craig. "When they look back and see what they did then and maybe reflect on what's not happening now. There are no manned missions being launched from America. I think that's sad to see from a nation that once led the way."

The film depicts the glamour of the Apollo astronaut's lifestyle. It shows crowds of people cheering the heroism of these new pioneers. The knights of the sky who conquered space. At the same time Last Man on the Moon shows us the effect on the astronaut's families. One scene shows Gene sitting with his grown-up daughter on a bench outside his ranch. "I remember when I came back, you said: 'You've been to the moon, NOW will you take me camping?", he says. Today Cernan admits that the astronauts' competitiveness, their drive to be the best, came at a personal cost.

"I cheated my family and friends out of a big chunk of my life," he says. "Particularly my daughter when she was growing up. I – like a lot of my colleagues – was so focused on the challenge that was placed in front of me. Our families suffered because of our selfishness."

The astronauts were unprepared for celebrity, while 1960s society – having watched the moon landings live on television – was getting a taste for it. Cernan had to learn how to sell the hero image, even if he himself didn't particularly believe in it.

"You – the press – made us sometimes holier than thou," he says. "And Nasa wanted to protect their image."

He remembers the Apollo 10 mission where his lunar module spun out of control. Cernan, temporarily panicked, shouted "Son of a bitch! What the hell happened!" on live television, prompting complaints to Nasa and the television networks.

"I didn't know I'd said it," he says. "I'm driving home from the airport with my wife and she said 'You were pretty salty, weren't you?'. And I said 'What are you talking about?' She told me what I said and that was the first time I heard it.

"I went into the office the next morning and I listened to the tapes and sure enough – that was me and I couldn't deny it. Nasa put the screws to me and said 'You gotta apologise'. But I needed to find my own way of apologising, so I said 'To those of you who I offended I'm sorry, to those of you who understand I thank you'.

"Then you get 50 letters that say 'Thank you – we would have said much worse. Nice to know you put your pants on one leg at a time just like everybody else'. And a bunch of other letters that say 'Thank you. You made us proud to be American. But how could you use such language in front of my kids?' It was a no-win situation."

Mark Craig says the Apollo astronauts were ill-prepared for what would happen to them after the mission was over.

"They spent a decade of their lives going for this massive goal, but when they achieved it, what were they to do next?," he says. "There are some comparisons to be drawn with the way we deal with war veterans. Certainly in previous wars they were expected to come back and keep a stiff upper lip and get on with their lives, whereas now we understand post-traumatic stress disorder and there is some kind of support available. I don't think there was much in the way of post-mission counselling."

Last Man on the Moon deals in nostalgia for space exploration, for the America of the time, and even – whether Cernan likes it or not – for brave people that we can idealise. All of these have become twisted in the years since the space race, but Cernan believes we can – and will – recapture that sense of romanticism.

"I can look kids in the eye and say 'You're going to take us back to the moon and then on to Mars'. I can say that with a straight face because I believe it. All we need to do is give them the same opportunities given to me.

"Curiosity is the essence of human existence: Who are we? Where do we come from? Where are we going? Was there life on Mars? Was there water? Is there life out there? The frontier of space is just a follow-on – we're following on in the footsteps of those who preceded us, and hopefully we'll give those that follow-on in our footsteps the same opportunity that we had.

"The press have labelled me The Last Man to Walk on the Moon. I'd like to think of it as the last man of Apollo to walk on the moon. We're going to go back again."