I would say the majority of people who purchase exercise bikes don't use them. However, if you really think your husband will use it (ie: does he already do some form of exercise?) then it is a worthwhile purchase. I would personally much rather have an eliptical machine or a treadmill but I suppose he wants the bike for a reason.
My husband has a fluid bike trainer which means you use your normal road/mountain bike and it holds the bike so you can pedal without the bike actually propelling forward. It has different resistance settings and you can still ride your bike outside anytime you want. It is a great addition to have if your husband already has a bicycle but wants to use it indoors (or in yucky weather).
I do agree that supporting your husbands' desire to exercise is important and so ultimately you should probably buy him "something" whether it be the exercise bike, treadmill, bike trainer, etc. I would be very hurt if I asked for a health-related item such as this and my husband didn't support me. I totally get where you are coming from as to whether or not it will just collect dust or actually get a lot of use, but you should probably just get it.
If you (and he) are open to a gently used exercise bike, Craigslist always has a million to choose from.

They are those people who bought one and never used it! lol Same with any other excercise equipment!
Edited to add: Regarding the eyesore comment, I couldn't agree more! BUT of course it is worth it if he uses it! I do know they make some models that fold in half when you're not using it so it will take up less floor space and you could slide it into a corner I guess.