Being captain of the Axiom is a dream job. You get to sleep 'til 12:25, you get to do morning announcements, and… that's about it. Everything else is run completely on autopilot, by AUTO, the automated captain's wheel. Like all the other humans, he's fine with this completely stationary lifestyle.
When the EVE probe returns with the plant, the concept of returning to Earth is more panic inducing than exciting. At this point, humans have been sedentary in space for 700 years. Why would they want to go back? The captain is relieved when the plant disappears, saying, "I guess things go back to normal, huh? […] Well, false alarm!" He's fine with the status quo, just existing and consuming.
But then WALL-E shakes his hand, and leaves a little dirt in it. The Captain does some research, finding out that this dirty stuff is "Earth," his home, a place of farming and dancing, and basically everything he doesn't have on the Axiom. Once he learns that, he tells AUTO "I don't want to survive. I want to live!" Sitting around drinking cupcakes in a cup is not living.
When AUTO tries to stop the Captain from returning to Earth, the Captain fights back. His most heroic moment comes when, after getting knocked out of his hoverchair, he stands on his big fat feet and walks over to AUTO as the tune from 2001 plays. It's one small step for a very, very fat man, and the beginning of another giant leap for mankind.