Archive for February, 2011

The ListView, one of the Windows Common Controls introduced with Visual Basic, provides a more polished look to your Userforms than a standard ListBox control. In order to use this control, you must add a reference to “Microsoft Windows Common Controls 6.0 (SP6)” (MSCOMCTL.OCX) to the project. As long as you’re not using the 64-bit version of Excel 2010, these controls will not present a problem. If you are using 64-bit Excel 2010, you will not be able to use the ListView control (and, as far as I know, Microsoft has no replacement in the works).

For those who are not familiar with the ListView control, this post will introduce you to the basics of configuring and populating one for your UserForm-centric Excel application. I’ll discuss some of the basic settings I prefer to give the ListView a polished, professional look. And finally, I briefly list some of the quirks and gotcha’s you need to be aware of.

Fire up Excel and let’s get started.

1. Create a new project.

2. In the Visual Basic Environment, add a reference to the common controls. Tools… References… Microsoft Windows Common Controls 6.0.

3. Add a standard module, name it “modControls” and add the following subroutine:

Public Sub SetCommonListViewProperties(ByRef ListViewToSet As MSComctlLib.ListView)

With ListViewToSet
  .View = lvwReport
  .FullRowSelect = True
  .Gridlines = True
  .MultiSelect = False
  .HideColumnHeaders = True
  .LabelEdit = lvwManual
  .HideSelection = False
End With

End Sub

This will give the ListView grid-like appearance and behavior.

4. Add a new UserForm to the project, name it frmListView.

5. Add a ListView control to the UserForm, name it “lvwTest” and size it to roughly fill the UserForm. Note: If you don’t see the ListView in the control Toolbox, then right-click the Toolbox, select “Additional Controls…”, and search for “Microsoft ListView Control, version 6.0”, and select it by checking the checkbox.

6. Add the following code to the UserForm_Initialize event:

Private Sub UserForm_Initialize()
Dim ch As ColumnHeader
Dim lngRow As Long
Dim ListItem As MSComctlLib.ListItem

SetCommonListViewProperties lvwTest

'Define the columns, even though the actual headers won't be visible.
With lvwTest.ColumnHeaders
Set ch = .Add(, , "First Name", 50, lvwColumnLeft)
Set ch = .Add(, , "Last Name", 100, lvwColumnLeft)
Set ch = .Add(, , "Company", 100, lvwColumnLeft)
Set ch = .Add(, , "Title", 50, lvwColumnCenter)
End With

'Now load some data into the ListView
With lvwTest
For lngRow = 0 to 4
Set ListItem = .ListItems.Add(, , "FirstName" & CStr(lngRow))
ListItem.SubItems(1) = "LastName" & CStr(lngRow)
ListItem.SubItems(2) = "Test Company"
ListItem.SubItems(3) = IIf(lngRow = 0, "President", "Drone")
Next lngRow
End With

End Sub

7. Test the ListView. To keep things simple, just open the Immediate Window in the VBE and type “frmListView.Show“. You should see your form and ListView with test data.

In addition to presenting the data nicely, the ListView also can be easily coded to sort based on the column header clicked. It’s a well behaved drag & drop source/target and exposes a full set of Enter, Exit, ItemClick, ItemCheck, BeforeUpdate and AfterUpdate events. If only it could be edited in place, it would rival a full featured grid control…

In the “SetCommonListViewProperties” listing above, I set the .HideColumnHeaders property to True because I find the built-in column headers to be dull and inflexible. They’re button-gray and cannot be formatted.

ListView with built-in column headers

ListView with built-in column headers

I prefer to create my own column headers using Label controls. They can be formatted with contrasting colors and a wide choice of fonts to give the ListView a bit more pizazz.

ListView with custom column headers

ListView with custom column headers


* The ListView does not behave well if placed on a container, such as a panel or frame control, that is subsequently toggled between being hidden and shown. It tends to re-position itself in the top left corner of the container. Same goes for a Tab or Multipage control. I once attempted to place ListViews on several pages of a Multipage control. The first time you view the page, the ListView is plastered in the upper left corner of the container. If you click on a different page and then back to the offending page, the ListView control is back in its proper position. I tried several things to fix it (i.e. explicitly setting the Top, Left properties in the Initialize and Activate events of the UserForm; explicitly refreshing the UserForm; re-positioning the ListView within the Click event of the Multipage control), all to no avail. I finally discovered a kluge: In the Click event of the Multipage control, if I make all the ListViews invisible and then visible again, the problem goes away.

* The x and y arguments for the MouseDown or OLEDragOver events must be manipulated before they can be passed accurately to the HitTest method of the ListView. This is a known issue and is addressed on many, many Q&A forums, so I’m not going to go into any detail here. Just do a search for “HitTest Excel UserForm” and you’ll see what I mean. This also pertains to the TreeView control.

* Clicking the “(Custom)” property of the ListView in order to use the graphical interface for defining columns, you may see a “Class Not Registered” error. This is something you’ll more commonly see with Windows Vista and Windows 7 and is caused by a missing or un-registered DLL called Msstkprp.dll. Do a search for this to get the details of how to download and register it.

The post is mainly for those of you who are not familiar with the Windows Common Controls. The ListView control and the TreeView control can really add polish and a professional appearance to your UserForm-centric Excel applications. You should familiarize yourself with both controls, and delve a bit more into the functionality that you can tap into for your next project.



Read Full Post »