A council cabinet member has said his decision to resign was made with "great sadness and disappointment" after health issues forced him to step down