Example: Astronaut Tim goes on an EVA to perform an emergency repair after a micrometeorite damaged the space station. A malfunction of his space suit kills him. Everyone agrees that Tim died a hero doing a high-risk activity that needed to be done, and almost no one would say it means we can't do spacewalks or can't send astronauts to space anymore.
Compare to Challenger or Columbia: The whole crew dies randomly on the way up or down during a routine mission. A program which became known for having like, a 10% chance of that happening would be horrifying (in my opinion).
But Shuttle was an economic as well as a safety fiasco. So there was no good motivation to keep using it.
The biggest tragedy of the Shuttle program was the 12 figure cumulative budget it consumed, equivalent to many thousands of lives.