A museum housed in an historic city centre church is to close.