This is not going to be about musicals, just so you know. I went through a phase where I listened to some of them, and I confess I still like "Les Mis." I do enjoy opera, I have seen "Cats" .... once .... and that's about where it ends. (Choral music is a different story, and Bugs Bunny is in a category all by himself.)
No, this is a rumination on the role music plays in storytelling. The obvious examples would be modern films, where the score has become an important part of the story on the screen. "Jaws" might not have been as effective with John Williams' foreboding score - and the wisdom of when not to use it. One of the most famous scenes in the movie, that leads to one of the more well known quotes ("We're going to need a bigger boat.") is one that happens without music. By that point in the movie the audience had come to expect the cue, and when it wasn't there to suddenly see the shark creates the shock and surprise that make the scene work.
But there are more subtle examples, too, including ones that happen behind the scenes. I tend to score the scenes in my head as I write, or sometimes before I write. There have been certain scenes in my stories that took at least some indirect inspiration from the music I listen to. (And a scene on the highway that owes its existence to Nine Inch Nails.) This is probably a by-product of having grown up in an era where the music in movies took on a much more prominent role and became a much more intrinsic part of the story.
I say this purely off the top of my head, as aside from a few notable examples like "The Great Escape" and others, I don't remember a lot of the classic films making as much or as significant use of theme music. I'm not sure if it's an accurate statement, but it seems to me that it's a modern trend... though, again, I could be wrong. As I write I have a few more examples springing to mind, but even "Lawrence of Arabia" which has this broad, sweeping theme that suits it didn't quite make use of that theme and others in the same way that films such as "Star Wars" made use of their musical cues.
Of course, I can't actually set my books and short stories to music. I can mention music in the story, as other authors have done, but I have to confess when I first read "The Gunslinger" I was unfamiliar with "Hey, Jude" and so the importance of that piece of music to the story was lost on me. It didn't impact my appreciation for the overall story, but it did mean I wasn't privy to one of the nuances of it. I think this is a risk whenever you make a reference to a particular song. Either your readers will get it, or they won't, and odds are if they don't they may not be inclined to go look it up.
Consequently, though I almost have to listen to music when I write, I don't directly transfer what's coming out of my speakers onto the page.
Though if I get the chance to control what music gets used when my books become movies... well, that's a different story. Anyone have Danny Elfman on speed dial?