Sunday, January 16, 2011

VBA Composite Control Object Model

When I turned 16 and got my license, I took to the road in a 1988 Buick Somerset. It was a nice car, I’ll admit. But it wasn’t as cool as my friend’s beat-up 1984 Monte Carlo.

Not that I disliked my Somerset, but it’s just not cool to drive a car named after a retirement village in Florida when you’re in high school. In all honesty, I’d have traded my nice Somerset for my friend’s Monte with its dents, torn seats, dash cracks, stained carpets, and odd smells in a second. Why? Because when you grow up in a village of 200 (196 people, 4 llamas) surrounded by acres of cornfield, you make your own fun. And an old beater of a car was especially fun because such a vehicle was perfect for making “enhancements”.

Case in point, my friend made one such enhancement by tearing apart his fuzz-buster, burying the sensor in the dash behind the windshield, and tying the output to the seatbelt light. Not only was this a smart move (radar detectors were greatly frowned upon by the local law enforcement), but it was a lot of fun watching his seatbelt light go berserk every time we passed a cop on the road. Not surprisingly, my friend went on to co-found a company that specialized in reprogramming your average four-door grocery getter to humiliate a stock Camaro off-the-line. He’s done things with cars we couldn’t have dreamt of then.

So what’s this got to do with VBA?

Only that I have learned that some of the most useful and valuable things I own are things that I’ve repurposed. That’s why I love VBA. I’ve used it to find unique ways to solve problems, work around software limitations, and otherwise get Microsoft Office to do things that I very much suspect it’s not supposed to do. In short, VBA is how I repurpose Office, particularly Access.

For example, I recently had a post on the Microsoft Access Team Blog on creating dynamic callbacks for controls. That code opened up some possibilities with VBA that I’d never explored. So, I tinkered a bit.

What I came up with is the Composite Control Object Model. Briefly, it’s a way of managing event driven interactions between controls and forms or any other object that can exist in VBA. So far, I’ve used it to create custom visual effects (like the WebButton), bind controls / forms together for easier updating, and enable a TabControl object to have a RecordSource. I think it could do other things, too - like create a javascript-style menu system, or perhaps a filmstrip-style gallery control that manages images stored in a single Attachment field.

However, I’ve reached a point where I can’t really take it much farther. That is, I don’t have enough uses for it to make it really useful. That’s why I’ve started a CodePlex site for it.

Anyway, download the demo. Tear it apart. Use it to repurpose a seatbelt light or two… Whatever.

Just let me know what you think.


  1. Hi Joel, is there any way to download the demo associated with your post:

  2. Yes, you can get it from:

    I actually did two versions. The link above is an update of the original post, which includes an early version of the cControl_WebButton class as well as the original post's demo (I think).

    The original post's demo is available here:

    I apologize for the lack of hyperlinks. I'll add links to the blog and to the Codeplex site, now that you mention it.

  3. Thanks Joel, the link
    doesn't lead anywhere I'm afraid :-)
    Is the early version of the cControl_WebButton class included in the update or in the original post?

  4. The early version is in the updated database (the link you could get to, I think).

    I'll post both demos on the codeplex site.

  5. The original two demos on the cdeplex site...