Usually, this is a sentence on it's own and it's used after another sentence. It's a way of saying "I tried to help, but you cannot force someone to accept help."
So Dan's examples are good in terms of one person telling what they did and another person consoling them by using the sentence "You can lead a horse to water, but you can't make them drink." But sometimes, the person telling the story can use this sentence, too...
John: "I told him that using a planner would help him manage his time better but he refuses to use one and continues to fail."
Mike: "That's a shame--he's so smart and could do so many things if he could learn to manage his time better."
John: "You can lead a horse to water, but you can't make them drink."
Also, in spoken language you often hear people say the first half of the sentence without the second half. So in the example above, John's last line could be "You can lead a horse to water..." and he could end there. The second have of the sentence is assumed because the phrase is so commonly known (at least in the United States)