A former priest from the Diocese of Norwich has withdrawn from his nomination as the new Bishop of Sheffield after a furore over his traditional views on women holding positions of church leadership.