There are any number of times where you need a tripod:
- Where the camera needs to be operated remotely. For example were you want to include yourself in the shot (self portraits), or you are photographing sensitive wildlife and your presence would disturb the animals.
- Where you need to use a long exposure time. For photography at night or in other low light level environments where you can't hold the camera still enough for long enough.
- Where you need to keep the camera in exactly the same position. For example, in time lapse or HDR photography.
- Where you have a heavy lens (e.g. large telephoto). You often see sports photographers using a tripod.
Basically it comes down to the times you need to keep the camera in the same location for a long time, either for the single exposure or for multiple exposures then a tripod is pretty much essential.
In other cases where you just need additional support (say telephoto photography on a windy day) then a monopod might be enough additional support.