At that age, I've found that adventure for them is poking around the campground, looking for bugs under rocks, etc. Young children don't really seem to see scenery in the same way adults do. They're more focused close up. So even though you may like to go out and explore, make sure you allot a significant amount of time to places where you sit and let the kids do their thing. And if you're staying in a private CG, I'd make sure I got out every day to the shore of a lake, a small stream, etc.
One park that kids seem to love is Great Sand Dunes NP. Although June is getting a little late, there's an intermittent, very shallow creek that ebbs and flows along the base of the dunes. Sand castles, dams, all the things kids would normally do at a beach.
One other place kids love is St. Elmo ghost town above Nathrop, CO. Yes, a real true ghost town, not some tourist trap. Wooden sidewalks and everything. Across from the little general store is a place where people feed the chipmunks and squirrels. They sell little bags of feed at the store. So you can sightsee and then the kids get to see the little creatures begging for food.
As far as timing, you're picking a very busy time in Colorado. Anything around the 4th of July may be already booked solid.
Ken & Allison
2 Camping Cats (1 diabetic)
1996 4Runner, TRD Supercharger, Edelbrock headers
2007 Fleetwood Arcadia, Honda EU2000i
4 mountain bikes, 1 canoe, 4 tents, 8 sleeping bags, 2 backpacks
(You get the idea!)