Has he said what - exactly - it is about the idea of looking after his own kids which is so unappealing ? If it's a case of him being anxious about certain tasks then that is something that could be worked on with him being reassured and/or taught as necessary.
If, however, it's more "the idea" of it then it's going to be a lot harder to get him to agree. Is he super traditional ? ... does the poor diddums feel his masculinity would be threatened or something stupid like that ? Worse - does he feel "it" is "beneath" him ? ..... in which case, that means he regards himself as superior to you and too important to do what he probably sees as "subservient" and/or "drudge" work.
Really feel for you. There's this fantastic opportunity - not just for you - but for the whole family and it doesn't even sound as if he's prepared to sit down with an open mind, a piece of paper and a pen so all the pros and cons of each possible scenario going forward can be properly considered. IF, having done that sort of exercise, he could come up with concrete arguments against you taking this job, i.e. any negative aspects which would - overall - reduce the quality of family life (though it doesn't sound as if he would) then fair enough - then you might have to reconsider - or try to find solutions. But simply stubbornly insisting he doesn't like "the idea" of like some overgrown toddler saying "no" over and over is both ridiculous and incredibly selfish.
You - the plural you - are a family unit. No-one should be acting unilaterally and the two adults should be working together for the common good ... what's best for the unit overall. Christ knows most of us have to do that to a greater or lesser degree. That often means compromising, or accepting that you can't always have things exactly as you (singularly) want as if you were still a single person. Sometimes - with the best will in the world, it simply isn't possible for both parties to be completely satisfied with their lot .... obviously, in a good relationship, both parties work to try and make each other's lives as fulfilling as possible but real life often gets in the way and stuff like childcare and money means someone often has to take more of a "hit" so to speak than their partner ..... because, that sacrifice means that ultimately the family is going to be more secure, less stressed, and function better instead of struggling to make ends meet and chasing their tails.
Him giving up his current job doesn't mean he's on the scrap heap. It does sound as if there'd be some opportunity for him to work part time - for (more) adult company, change of scenery, personal challenge etc. and, for example, this could also perhaps be an opportunity for him to feel valued and sought after (if that's what's bugging him - though of course looking after his kids is very valuable indeed) by taking on a volunteer role in some capacity - not when he's looking after the kids obviously but when you're around ... something which might have been harder to contemplate when he was working FT.
And this whole thing doesn't mean that's how things are going to be FOREVER. Once the youngest starts school he might then be able to go back to work FT anyway as far less childcare would need to be paid for. etc., etc., etc. Sorry if I'm teaching you to suck eggs because I'm sure much of this will have already occurred to you.
I think he's being awful - the very least he owes you is a thorough conversation - or conversations - and proper consideration of the various measures needed and resulting changes were you to accept this role. It's completely unfair that you've "done your bit" so to speak and now, when if he took over for "his turn" your standard of living would improve, that he's being so petulant about it. If it's not good enough for him, why is it good enough for you ? Someone has to look after the children ... and to say he "can't cope" with dentist appointments and the like is pathetic .... what - exactly - is too hard about that ? And why does the default position have to be you when if it was him it'd be far more advantageous for all of you ?
Are you going to be able to live with the status quo knowing what a fantastic opportunity has slipped through your fingers ? How will you be able to cope with the resentment you'll almost certainly feel towards him ?
Another thought ..... is this down to jealousy ? ..... because it's you being flattered with a great job offer and not him ?