When to use manual mode on my camera? That was a question posed on the DPS forums this morning.
For me, the answer is almost all the time. I am forced into that because my favourite lenses only work in manual mode anyway on my D40 but I do not regret that. After all, I chose them.
The whole point of upgrading to a DSLR, for me, was to get a tool offering greater control. It is not just about exposure based on a measure of light across all or part of the scene but about skillfully capturing the play of light on and around your subject while also exploiting other effects like depth of field and freezing or blurring motion. The computers inside modern cameras are very good at doing sums but they still have not captured the ability to make aesthetic judgements and certainly not to fall in line with your own sensibilities about the image you are seeking to create.
Given instant review of the image and information overlays like the histogram and blinking highlights, it is not difficult to get a good exposure so, even with the fully-compatible kit lens, I tend to stay in manual mode unless I merely want to grab a few snapshots rather than capture artistic photographs.