Moments of extreme discouragement, where I decided all was lost:
After TMP.
Partway during season one of Next Gen. I missed the improvement in season two at first, because I just couldn't watch after season one. What's the TV-viewing equivalent of "traumatization"?
The grinding boredom of Next Gen seasons 4-7.
That was a more drawn-out gradual process of disillusionment.
Biggest time of disillusion-- right now. Trek controlled creatively by a corporation... no one left to keep things on track and fight Paramount. The mangling of the work of great original Trek designers with the "Remastering" ...
Revivals of hope for me:
Wrath of Khan-- despite it mostly being meaningless escapist space battles, things were improving I thought. I was wrong.
"Q Who"--- It dropped on me like a ton of bricks that they were serious about improving NG. Actually, that was the first inkling I ever had, that valid, new "Star Trek" was possible, after the original series. There were two or three greats before that in s2, but I'd been missing most NG ep's until that point.
The energizing, for lack of a better word, of DS9 as it worked its way toward the Dominion War. Season six of DS9 happened simultaneously with season four of Voyager, and things looked hopeful. That was the last such time I think.