Can I use "The curtain is closing on him" as a metaphor for having the opportunity/chance to do/get something, but something went wrong, so the curtain is closing on him(it's over).
The curtain is closing on him.
has the meaning that something related to "him" is coming to an end.
It can mean his opportunity is ending, but it has no meaning of "something went wrong". The opportunity could be ending for any number of reasons. Other metaphors might be
The sun is setting.
The light is dimming.
The curtain is closing on him
seems a bit unidiomatic. The usual and customary phrase for this is:
It's curtains for [whatever is ending].
The Free Dictionary has an explanation of this idiom. It can carry the sense of "something went wrong":
I needed to get a B in Advanced Calculus to continue, but I got only a C+, so it's curtains for the math major!
Using this phrasing, you could also say:
I needed to get a B to continue as a math major, but I got only a C+, so it's curtains for me.