Feeds:
Posts
Comments

Posts Tagged ‘Excel VBA’

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

Advertisements

Read Full Post »