Lost a full day sorting this out. Turns out that ESRI.ArcGIS.CatalogUI.SpatialReferenceDialogClass() will only work if you are binding to a Desktop license. Previously I was binding with this:
ESRI.ArcGIS.RuntimeManager.Bind(ESRI.ArcGIS.ProductCode.EngineOrDesktop);
But that resulted in an error when instantiating the class and I would get the error displayed below.
If I bind only to Desktop then the class works and the dialog opens.
I don't understand ESRI's logic in this but there it is. Seems at least I should have been able to use the 'EngineOrDesktop' binding. That must be a bug or I don't really understand that classification. And why include this class in ArcGIS Engine if you are not able to use it with an Engine only application? And a more understandable error message would be nice. I spent a bunch of time trying to determine if the CLSID was or was not registered. Go figure.