Curator Helen Molesworth on artists’ desire for fame, greatness or success, and their longing for the inclusion of queer identities in popular culture