Feeds:
Posts
Comments

Posts Tagged ‘ListView’

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
  .ColumnHeaders.Clear
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
.ListItems.Clear
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…

Preferences
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


Quirks

* 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.

Summary
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.

+MD

Advertisements

Read Full Post »

The Challenge…

Preferring to use ListView controls rather than standard Listboxes (because they look a lot slicker), the client tasked me with replacing all their Listboxes with ListViews with retention of all current functionality.  I soon discovered a problem with managing right-mouse-clicks on a ListView item. This post recounts the steps involved in taming this snarly beast.

(Note: If, for any reason, the ListView control is not available on your computer, see my post addressing installation and troubleshooting of the ListView, TreeView, ImageList, etc. controls)

Preparation…

This example was created using Excel 2007.

* Create a new macro-enabled workbook.

* Insert a new Userform and name it “frmHitTest” and change the Caption to “ListView HitTest Experiment” (or something like that).  Add a ListView control to the form, naming it “lvwTest”.  Finally, add a command button, name it btnExit, and set the Caption to “Exit”. Double-click the command button to open the btnExit_Click event and add the following code:

Private Sub btnExit_Click()
   Unload Me
End Sub

* Insert a new standard module and name it “modGlobal”.  Add a new subroutine called “InitializeApplication”, as follows:

Public Sub InitializeApplication()
     frmHitTest.Show
End Sub

* Open the code window for ThisWorkbook and edit the Workbook_Open event as follows:

Private Sub Workbook_Open()
     InitializeApplication
End Sub

* Right-click on the project name (usually “VBAProject”) and select VBAProject Properties.  Use the dialog box to change the Project Name to “ListView HitTest” and change the Project Description to “Proper Way to Perform HitTest on ListView Control”.  Click OK to store these changes.

* Next, let’s set the appearance of the ListView and populate it with some dummy data.  Open the code window for frmHitTest and add the following code in the Userform_Activate event:

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

'Define ListView appearance
With lvwTest
   .View = lvwReport
   .FullRowSelect = True
   .Gridlines = True
   .HideSelection = False
   .ColumnHeaders.Clear
End With

'Create ListView columns
Dim ch As ColumnHeader
With lvwTest.ColumnHeaders
   Set ch = .Add(, , "ID", 40, lvwColumnLeft)
   Set ch = .Add(, , "First Field", 60, lvwColumnCenter)
   Set ch = .Add(, , "Second Field", 60, lvwColumnLeft)
   Set ch = .Add(, , "Last Field", 100, lvwColumnCenter)
End With

'Populate the ListView with dummy data
With lvwTest
   .ListItems.Clear
   For lngRow = 0 To 40
     Set ListItem = .ListItems.Add(, , CStr(lngRow))
     ListItem.SubItems(1) = "FirstField" & CStr(lngRow)
     ListItem.SubItems(2) = "SecondField" & CStr(lngRow)
     ListItem.SubItems(3) = "LastField" & CStr(lngRow)
  Next lngRow
   .Refresh
End With

End Sub

* Save the project as a macro-enabled workbook, naming it “ListViewHitTest.xlsm”.

* Run the project, either by closing then re-opening the workbook or by opening the Workbook_Open method of the “ThisWorkbook” code module and pressing F5.

Initial Appearance of ListView

Initial Appearance of ListView control

Now, let’s proceed to build the functionality that is required:

  • Capture click of right mouse button (i.e. right-click).
  • Capture the ID of the list item that was right-clicked.
  • Show a simple Msgbox that confirms the right list item has been right-clicked. (i.e. in a real application, this right click would most likely cause the display of a pop-up menu with user choices).

The Solution…

In an attempt not to overcomplicate things you might be tempted to use the MouseDown event of the ListView, accepting all arguments at face value, as follows:

Private Sub lvwTest_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, _
  ByVal x As stdole.OLE_XPOS_PIXELS, ByVal y As stdole.OLE_YPOS_PIXELS)
    Dim item As MSComctlLib.ListItem

    'Only capture the standard right-clicks; otherwise get out.
    If (Button <> xlSecondaryButton) Or (Shift <> 0) Then Exit Sub
    'Just pass along the event's x and y arguments.
    Set item = lvwTest.HitTest(x, y)

    MsgBox "Item: " & item.ListSubItems(1) & " has been right-clicked!", vbInformation, "Capture Right-Click"
End Sub

Sounds like a reasonable approach.  But this code gets erratic results.  No matter where you right-click, it seems to think you’ve clicked on the first visible item near the top of the list.  In the example below, I have right-clicked List item with ID# 17, but the Excel thinks I have right-clicked on ID #0 (at top of list).  Obviously not the results we wanted.

List View with incorrect list item selected

List View with incorrect list item selected by HitTest

After a bit of MSDN research I find that the ListView MouseDown event’s x and y arguments specify the number of pixels from the top and left of the ListView control.  But the HitTest method requires x and y parameters in Twips.  So, now we must use Windows API functions in order to perform this conversion.

* Add the following global Constants and API Function declarations to modGlobal:

'Windows API Constants
Public Const LOGPIXELSX = 88
Public Const LOGPIXELSY = 90

'Windows API Function Declarations

'Get a handle to the Device Context (a drawing layer) for a window
Public Declare Function GetDC Lib "user32" (ByVal hWnd As Long) As Long

'Get the capabilities of a device, from its Device Context
Public Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDC As Long, _
    ByVal nIndex As Long) As Long

'Release the handle to the Device Context, to tidy up
Public Declare Function ReleaseDC Lib "user32" (ByVal hWnd As Long, _
    ByVal hDC As Long) As Long

* Go back to frmTest and re-enter the code for lvwTest_MouseDown event as follows:

Private Sub lvwTest_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, _
 ByVal x As stdole.OLE_XPOS_PIXELS, ByVal y As stdole.OLE_YPOS_PIXELS)

   Dim item As MSComctlLib.ListItem
   Dim lngXPixelsPerInch As Long, lngYPixelsPerInch As Long
   Dim lngDeviceHandle As Long

   'Only capture the standard right-clicks; otherwise get out.
   If (Button <> xlSecondaryButton) Or (Shift <> 0) Then Exit Sub

  'We must determine the Pixels per Inch for the display device.
   lngDeviceHandle = GetDC(0)
   lngXPixelsPerInch = GetDeviceCaps(lngDeviceHandle, LOGPIXELSX)
   lngYPixelsPerInch = GetDeviceCaps(lngDeviceHandle, LOGPIXELSY)
  ReleaseDC 0, lngDeviceHandle

  'Convert the event's x and y arguments from Pixels to Twips
  Set item = lvwTest.HitTest(x * 1440 / lngXPixelsPerInch, _
      y * 1440 / lngYPixelsPerInch)

   MsgBox "List ID #: " & item.Text & " has been right-clicked!", _
      vbInformation, "Capture Right-Click"

End Sub

* Run the project again and see what results you get.   Seems we’re on the right track now!  The HitTest is now able to correctly identify the ListView item that I right-clicked.

HitTest now correctly identifying Item #17 as right-clicked item.

HitTest now correctly identifying Item #17 as right-clicked item.

Great!  Only one problem remaining.  The ListView item that was right-clicked is not visibly selected (i.e. highlighted).  It looks like item #0 is selected even though the mouse cursor is clearly over item #17.  This problem is easily corrected, by forcing the selected item after the HitTest in the lvwTest_MouseDown event on frmTest.  Add the lines below, in blue text, after the existing HitTest line:

  Set item = lvwTest.HitTest(x * 1440 / lngXPixelsPerInch, y * 1440 / lngYPixelsPerInch)
 If Not item Is Nothing Then
     Set lvwTest.SelectedItem = item
 End If

Voila!  Now everything is working as intended.  You can see that item #17 is highlighted, and the MouseDown code is identifying that item #17 was the one that was right-clicked.

ListView with right-click and item highlighting working correctly.

ListView with right-click and item highlighting working correctly.

Summary…

It’s not obvious that the x and y coordinates needed to be converted from one method to the next, but that was the case.  Even though it’s a bit of a pain to have to add all those API function declarations and constants just to perform the conversion, they can be wrapped in re-usable class modules and re-used from project to project.

Also, depending on the complexity of your Userforms, you may have to rearrange this code a bit.  For example if you have other code that is fired when a ListView item is clicked, you may have to prevent events from recursively firing.  In a recent project, I wanted to show a popup menu when the user right-clicked on a ListView item, and execute other code as well.  I ended up having to put the HitTest and the setting of the “SelectedItem” in the MouseDown event, while the call to the popup menu was placed in the MouseUp event.  If you’re able to implement the code in this post, and duplicate the results, but you find that quirky things are happening, don’t be afraid to experiment with using the different ListView event procedures to try and remedy the problem.

Snarly beast tamed.

-MD

Read Full Post »