What form controls can a VBA project access from Office 2007?

Question

In a VBA project, you can create a UserForm and add controls to it using the toolbox window. By default, the toolbox window contains the simple form controls such as Label, TextBox, ListBox, etc.

You can add additional controls if they're installed on your machine: right-clicking "additional controls" on the toolbox shows a whole screed of controls. However, this does not give any indication of where they came from.

I'm interested to know what controls are guaranteed to be available on a machine that has Office 2007 installed. Is it just the standard ones, or are some of the "additional controls" also guaranteed to be available (either because they come with Office 2007, or because they exist on all the versions of Windows that Office 2007 will run on)?

For example, I'm surprised that there's no treeview control even in Office 2007. In my "additional controls" list I see the "Microsoft TreeView Control v6", but I don't know whether I can rely on that being present for all my Office 2007 users.

Answer 1

The controls you see are part of FM20.DLL; which is part of the Office Install.

You can see that they are all part of the same DLL by looking in the "Location" portion of the "Additional Controls" box.

AFAIK, those are the only controls guaranteed available with most Office installations.

So that being said, let's see what can be done specifically for each control you ask about in your comments:

1. "grid" capable list
2. hierarchical tree view
3. calendar control

---

The Grid

It's going to come down to your requirements, but you may be able to get away with using a listbox. The listbox in VBA has a few of properties that make will make it Grid-like.

For example, let's say a worksheet in Excel looks like this:

ID001   Value 1 Description 1
ID002   Value 2 Description 2
ID003   Value 3 Description 3

The listbox supports multiple columns, so you can make these values show up by setting the listbox property ColumnCount to 3 and writing the following code:

    Me.ListBox1.ColumnWidths = "50;100;200"
    Me.ListBox1.RowSource = Sheet1.Range("A1:C3").Address

You will get something like this:

Want to hide a column? No problem, change the width of the ColumnWidths property to zero for the column to hide:

Me.ListBox1.ColumnWidths = "50;0;200" 'Hide column 2

What good is a grid if you can't select things from it, right?

On the listbox, change the ListStyle property to frmListStyleOption and then change MultiSelect to frmMultiSelect.

That will give you a listbox that looks more like a grid:

---

Calendar

08/17/2012 UPDATE: Read this post. One of the guys who answered created his own calendar control.

A calendar control exists for Office (mscal.ocx):

However, the right conditions must be met:

1. Access must be installed on the clients machine
2. The Office version cannot be Office 2010 as it was removed with this version (See Features removed from Microsoft Access)

You can still get the calendar control to work with 2010 and/or non-Access Office installations, but it takes additional steps on the client's machine.

1. Download mscal.ocx
2. Extract it to windows/system32 directory
3. Register it

---

TreeView

If it's acceptable for you clients to install some EXEs, you could have them install Microsoft Visual Basic 6.0 Common Controls. That will give you the TreeView control.

I would be willing to bet that most machines (particularly older ones on Windows XP) already have these OCXs installed; thus the installation may not be necessary.

The biggest problem is that you're deploying OCXs on client machines and that gets frustrating from a support standpoint.