The move, unprecedented in modern times, came weeks before the Rev Dr Angus Morrison was due to make his first key engagement, chairing the Church's annual gathering in May.
The current Moderator, Rt Rev Lorna Hood, and MSP Murdo Fraser were among those to offer support for Mr Morrison.
The year-long ambassadorial role involves a rigorous diary that includes international travel.
The Church said in a statement that Mr Morrison "is undergoing treatment and it is hoped that over the remainder of this year his health will be fully restored".
It added: "In the meantime his well-being depends on having time and space to fully recover".
Mr Morrison, 60, Minister of Orwell and Portmoak Church in the Presbytery of Perth, said the move was made with "huge regret".
He said: "This has been a very painful decision but deep down I feel it is the right one. I am undergoing medical treatment and anticipate an operation ahead.
"The demanding role of Moderator requires someone to be operating on all cylinders. I do not have the necessary reserves just now. It is with huge regret and reluctance I make this decision.
"My heartfelt prayers will be with all those on whom unexpected burdens now fall. May God's guidance, strength and peace be given to each one."
Mrs Hood tweeted: "Very sad that ill health forces Kirk's Moderator-Designate to withdraw. I'm sure the heartfelt prayers of whole Church will be with Angus tonight."
Mid Scotland and Fife MSP Mr Fraser posted: "Sorry to hear that Dr Angus Morrison has had to withdraw as Moderator-Designate. Will be a loss. Thoughts with him and family."
Mr Morrison is married to Fife primary school teacher Marion and they have four children between the ages of 16 and 27.
The Rev John Chalmers, Principal Clerk for the Church of Scotland, said: "We ask the whole Church to remember Angus and his family in their prayers and we hope that his health is fully restored in due course.
"I know the General Assembly will be enormously sympathetic to Angus's position and will no doubt want him to fulfil the role of Moderator at some time in the future when he has made a full recovery."
Mr Morrison, who has a degree in Classics and speaks Italian and Gaelic, has served as a chaplain to the Queen and began his church career in the Free Presbyterian Church of Scotland.
There is no precedent in living memory, and it is suggested since at least the 16th century onwards, for a Moderator-Designate standing down.
The Church faces issues such as the contentious matter of the next stage in the move towards allowing gay clergy at the assembly.
The near-1000 Church representatives who attend the Edinburgh gathering will again be navigating through the debate first sparked by the appointment of openly gay Rev Scott Rennie to an Aberdeen Church in 2009.
The committee that decides on Moderators will now be recalled to bring forward the name of another nominee whose duties will include keeping order, leading daily worship, ruling on points of order and signing documents on behalf of the Assembly as well as the year-long representative role around the UK and the world.
The new Moderator-Designate is likely to be announced on Wednesday April 2.