You really can't mess with it when you're dealing with the kids, unless you have a patient wife who'll keep an eye on them when you set up your tripod and take the photo.
As for why and what you'd do with it, 99% of the time they are used for night photography, including fireworks shots. A decent tripod makes getting nice fireworks and night shots fairly trivial, and you'll produce much, much better shots than if you handheld it and took the same photo.
My wife is nice enough to let me play with my tripod a little with the kids around, but she also lets me take solo trips out some nights when one of the parks is open particularly late, at which point I can use the tripod and shoot at my own pace.
If you're not shooting for getting any real fancy night photos and expect to be with your kids the whole time, you probably can leave it at home... or if you have room, throw it in the luggage so you have the option of using it if the opportunity presents itself.
