Feeds:
Posts
Comments

Archive for the ‘Userforms and Controls’ Category

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 »

Although Excel UserForms do not inherently support minimize/maximize functionality, I got a request stating that the application users needed to be able to open multiple Userforms simultaneously, minimizing, maximizing and switching between. Or the world will end. Make it happen.

No need to sweat if you get marching orders like this. Armed with the information in this post you’ll have ’em minimizing and maximizing to their heart’s content.

As always, let’s start from the beginning:

* Create a new macro-enabled Excel workbook.

* Switch to the VBA editor and add a new code module. Name it modAPI. Enter the following code in the Declarations area. These are all the required Windows API function declarations and constants that will be required to allow standard window minimize/maximize behavior in our UserForms:

Public Const WS_MINIMIZEBOX = &H10000
Public Const WS_MAXIMIZEBOX = &H20000
Public Const GWL_STYLE = (-16)
Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long 
Public Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long
Public Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Public Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long

* Next, add the following function to the modAPI module:

Public Sub AddMinMaxButtons(ByVal FormCaption As String, ByVal MinButton As Boolean, ByVal MaxButton As Boolean)
'Add either minimize button, maximize button, or both buttons to the UserForm with the specified caption.
'IMPORTANT: If a UserForm's caption is changed dynamically in your program, this subroutine must be run again (with the new Caption) or the min/max buttons will disappear.
Dim hWnd As Long
Dim lngStyle As Long
hWnd = FindWindow(vbNullString, FormCaption)
lngStyle = GetWindowLong(hWnd, GWL_STYLE)
If MaxButton Then
    lngStyle = lngStyle Or WS_MAXIMIZEBOX
End If
If MinButton Then
    lngStyle = lngStyle Or WS_MINIMIZEBOX
End If
SetWindowLong hWnd, GWL_STYLE, lngStyle
DrawMenuBar hWnd
End Sub

* Create a new UserForm. Change the Caption property to “My Test Form”

* In the UserForm_Activate event of the new form, enter the following code:

Private Sub UserForm_Activate()
'Set the min/max button setting for the form.
 AddMinMaxButtons Me.Caption, MinButton:=True, MaxButton:=True
End Sub

* Next, add the following code to the Workbook_Open() event of the ThisWorkbook module:

Private Sub Workbook_Open()
    UserForm1.Show vbModeless
End Sub

At this point you can save the workbook, close it, then open it again (or just run the project from the Workbook_Open() event in the ThisWorkbook object). You’ll see that the UserForm opens, it has proper Minimize and Maximize Buttons and the underlying worksheets are still accessible.

It’s that easy.

Finally, to demonstrate that Min/Max buttons will disappear if the UserForm’s caption changes, open the UserForm and add a Command Button. Set the Name property to cmdChange and the button’s Caption property to “Change Form Caption”. Open the code behind the button and enter the following code:

Private Sub cmdChange_Click()
'Change the form's caption and observe what happens to the Min/Max buttons.
    Me.Caption = "New Test Caption"
End Sub

UserForm for testing Min/Max Buttons

* Once again, save, close and re-open the workbook. When you see the UserForm, click on the “Change Form Caption” button. Immediately you will see the form’s caption change, and the Min/Max buttons disappear. So always insure that if your form name can change on the fly, make sure you add another call to the AddMinMaxButtons method after the caption changes.

-MD

Read Full Post »

Snappy ListBox Population

The Challenge…

Retrieve 10,000 rows from a database table and populate a ListBox control on an Excel Userform; and make it snappy. While 10,000 rows is not a “large” dataset in our business, it is certainly large enough to drive your application users crazy as they wait for their worksheet or List controls to be populated with all this information.  Clearly, looping through a 10,000 row Recordset and populating the ListBox one row/column at a time is not going to meet the requirement.  This post will demonstrate how to create the Userform and ListBox control, retrieve the dataset from the database, and populate the ListBox in an efficient manner.

The Solution…

The ListBox control itself is quite optimized for handling large sets of data.  The key to the speed is in how we retrieve the data from the database and, equally important, how the data is inserted into the ListBox control.

  • Create a new workbook.
  • Open the VBA Editor (Alt-F11).
  • In the Project Explorer, right click on “VBAProject (Book1)” and select VBAProject Properties.
  • Change the Project Name to “SnappyListBox”.
  • Change the Project Description to “Populate ListBox with 10,000 rows from database”
  • Click OK, then click the Save button.  Create a new folder called “SnappyListBox” and name the workbook “SnappyListBox.xlsm” (being careful to save the workbook as a Excel “Macro-Enabled Workbook (*.xlsm)”).
  • Next, create a new Userform (Insert… Userform).  Change the Name property to “frmSnappy” and the Caption to “Snappy ListBox Form”.
  • Change the form’s Height to “400” and Width to “600”.
  • Add a ListBox control to frmSnappy.  Set Top=30, Left=30, Height=300, and Width=540.  Change the ListBox’s name to lstSnappy.
  • Open up the frmSnappy code window and enter the following code in the Userform_Initialize event procedure:

Private Sub UserForm_Initialize()

    'Set basic formatting for the ListView

    With lstSnappy

        .ListStyle = fmListStylePlain

        .BorderStyle = fmBorderStyleSingle

        .ColumnCount = 6

        .BoundColumn = 2

        .TextColumn = 3

        .ColumnHeads = False

        .ColumnWidths = "40pt;55pt;140pt;75pt;40pt;185pt"

        .MultiSelect = fmMultiSelectSingle

        .SpecialEffect = fmSpecialEffectEtched

    End With

End Sub
  • This just sets the basic attributes of the ListBox, such as the number of columns of data it will have, etc.
  • Save the Project.
  • Next, open up the code window for ThisWorkbook and locate the Workbook_Open event.  Add code to show the Userform when the workbook first opens:
Private Sub Workbook_Open()

    frmSnappy.Show

End Sub
  • Add a Command Button to the form, in the lower right hand corner.  Name it “cmdLoadList” and change the caption to “Load List”.
  • Save the Project once again.  Then, with the Workbook_Open() function still open and selected, press F5 to run the project.  (You could also close the Workbook and open it again to trigger the execution of Workbook_Open(), but just running it from the development environment is much easier).
  • You should see your form appear with a blank list and Command Button, looking something like this:
Basic Userform with empty ListBox

Basic Userform with empty ListBox

  • If any errors are raised, review all the instructions above and try to determine where it went wrong.  Once you confirm that all is working correctly, close the Userform and let’s move on to the next step.

Now that the ListBox is prepared, we need to configure the environment and write the code to retrieve the data from the database.  We will be using a SQL Server database and will be connecting from VBA via ADO.  At this stage, you’ll need to have created a database called “TestDB” and you will need to know the server name on which the database resides and how to build a connection string to connect.  On my end, I have created a test table called “large_test_set” with the following columns defined:

CREATE TABLE [dbo].[large_test_set](
    [price_date] [smalldatetime] NULL,
    [item_id] [varchar](10) NULL,
    [issuer] [varchar](100) NULL,
    [avg_mkt_price] [float] NULL,
    [avg_rating] [varchar](5) NULL,
    [dominant_industry] [varchar](100) NULL
) ON [PRIMARY]

My test table is currently populate with about 14,000 rows.

NOTE:  If you’re unclear about any of the database stuff, you can still follow along with the post to see how the ListBox is managed (which is really the purpose of the post), although you won’t actually be able to run it.  However, it is still probably worthwhile to continue reading.

With everything on the database side now prepared, let’s return to the VBA code.

  • Return to the VBA Editor.  On the menu bar, click Tools… References.  Locate “Microsoft ActiveX Data Objects 2.8 Library” (or 2.7, or 2.6, whatever the latest version of ADO is on your computer; any of the version from the last few years will work for this example).  Check off the ADO selection and click OK to close the dialog.

Now the entire ADO object model is available for use in the project.  Normally, at this point I would plug in one of my data access class modules and go.  But since this is not really a post about ADO or data access, we’re going to keep it very basic and just include all the data access code directly in the form module.

  • Open up the code module for frmSnappy and add the following constant and variable (remember to substitute your values in the connection string constant):
Private Const CONN_STR = "Provider=SQLNCLI10;Server=MyServer;Database=TestDB;Trusted_Connection=yes;"
Private mobjConn As ADODB.Connection
  • Next, add the following module level function to frmSnappy:
Public Function GetRecordsetToArray(SQLToExecute As String) As Variant
    'Load recordset data into an array and return the array to the calling routine.
    'If the recordset does not return any data, this function simply creates a
    '  single element array and populates it with  "No matching records...".
    Dim rst As ADODB.Recordset
    Dim arrData As Variant
    Dim lngX As Long

    On Error GoTo GRAError
    If mobjConn Is Nothing Then
        Set mobjConn = New ADODB.Connection
    End If
    mobjConn.ConnectionString = CONN_STR
    mobjConn.Open
 
    Set rst = New ADODB.Recordset
    With rst
        .CursorLocation = adUseClient
        .LockType = adLockReadOnly
        .Open SQLToExecute, mobjConn, adOpenStatic
 
        If Not (rst.EOF) Then
            'Disconnect the recordset
            .ActiveConnection = Nothing
            'Get the field count
            lngX = .Fields.Count
            'Load the array
            arrData = .GetRows()
        Else
            'Recordset is empty; create dummy array record
            ReDim arrData(0, 0)
            arrData(0, 0) = "No matching records found in the database"
        End If
    End With

    GRAResume:
        GetRecordsetToArray = arrData
        mobjConn.Close
        Set rst = Nothing
        Set mobjConn = Nothing
        Exit Function
 
    GRAError:
        MsgBox "Error: " & Err.Number & vbCrLf & "Description: " & _
            Err.Description & vbCrLf & "Source: frmSnappy::GetRecordSetToArray", _
            vbCritical, "Database Error"
        Resume GRAResume
End Function

Note that this function uses the .GetRows() method of the ADO Recordset object.  GetRows() will take the Recordset data and put it directly into an array.  It’s super-fast and reliable.  Great.  Except for one last problem:  GetRows() load the array such that the first dimension of the array are the Fields/Columns and the second dimension of the array are the Rows.  Which is exactly the opposite of what our ListBox’s .List property is expecting.  So, if I were to code “lstSnappy.List = arrData”, it would not give me the results I’m looking for since the data would be transposed.

Now it’s no real difficult thing to write a routine that will transpose an array, but wouldn’t it be better if the ListBox just had a way of handling this?  Well, it does.  The .Column property.  This poorly documented property will accept an array as its value, transposing it automatically.  (I haven’t seen this usage of .Column documented anywhere, but I’m sure it is; I just haven’t looked hard enough.  But I think it’s safe to say that it isn’t an easy thing to find).

  • Finally, write code for the Command Button to execute the database query and population of the Listbox:
Private Sub cmdLoadList_Click()
Dim strSQL As String
Dim arrData As Variant
'Clear the list first
lstSnappy.Clear
'Retrieve the data into the array
strSQL = "select price_date, item_id, issuer, "
strSQL = strSQL & "avg_mkt_price, avg_rating, "
strSQL = strSQL & "dominant_industry" & vbCrLf
strSQL = strSQL & "from large_test_set" & vbCrlf
strSQL = strSQL & "order by price_date DESC"
arrDat = GetRecordsetToArray(strSQL)

'Populate the ListBox.
'Transpose and load the array in a single statement.
lstSnappy.Column = arrData

End Sub

Now run your project.  Click the “Load List” command button and see how quickly the thousands of rows are loaded into your ListBox.  Looping through a Recordset and using AddItem on the ListBox will take considerably longer, and will require more code.  Hopefully, this post has been clear enough that you’re seeing the same results I am.  Experiment with other techniques, such as dumping the Recordset onto a worksheet and setting the RowSource property of the ListBox to point to this range.  In my experimentation, the .GetRows/Column method is still considerably faster (and not a kludge like some other methods.

Populated ListBox control

ListBox control populated with 14,000+ Rows

Summary…

The combination of the .GetRows() method of the ADO Recordset, combined with the mysterious .Column property of the ListBox, provides an optimal solution for quickly loading a ListBox with large data sets.  This technique works with Excel 2003 and 2007, but I have not yet tested it with Excel 2010.  Soon.

-MD

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 »