Latin might be considered a "dead" language in regular society, but if you're deep into the traditions of the Catholic Church, or you like the Extraordinary Form of the Mass, or the Novus Ordo Mass in Latin, then knowing even the basics of the language can certainly come in handy.
As you know, a foreign language education is important. Even if you aren't into any of the above, a background in this language can be very helpful when it comes time for your student to take their college ACT or SAT tests. Knowing the language can help your students do better in vocabulary and even spelling. Many of our English words find their root in Latin.
If you still aren't convinced, give it a try and see how much fun learning even the basics can be. We've only studied the basics, but I can tell you that it has been helpful when we've attended Mass and we hear the Mass parts said or sung in Latin.
Like other languages, its good to start introducing the basics when the kids are young and then, as they get older, you can introduce them to some of the more difficult concepts. By high school, they could even be writing papers using their accumulated knowledge.
We've only personally done the Prima Latina book and we loved it. We bought the DVDs which helped immensely. I know many other families that have worked with the Latina Christiana series and have found it to be a good program.
So far, everything we've seen from Memoria Press has been very well done and very educational. Check out the links below for more information and to purchase your set!
Like every other subject, there are many other programs to choose from. Here are a few that I've heard about repeatedly that you might want to also look into.Latin: First Year (Henle Latin)