Dutch Gemini's Weblog Pages

December 17, 2010

How to develop a ListView with Tristate checkboxes on Excel’s Userform

Filed under: Excel,Programming,VBA — dutchgemini @ 3:31 pm
Tags: , , , ,

In an Excel Add-In application I’m developing I need to provide a way to set options to a variable amount elements I’m dealing with.  The elements are stored in a ListView ActiveX (from MSComCtlLib). The interface permits a partial selection of the elements and on this partial selection I want to provide the mentioned functionality. In practice, the user makes a selection, calls a dialog with a certain amount of options, sets whatever option and saves.

To deal not only with the previous setting of the options but also with the possibility to ignore an option for the selected elements, I use a TriState checkbox. Just to remind, a Tristate checkbox has the following 3 values:

  1. False meaning not checked displaying an empty box,
  2. Null meaning indeterminate displaying a grayed checkmark, and
  3. True which means checked displaying a black checkmark.

As long as the options you can set is known and fixed, you can populate the options dialog with a static amount of Tristate checkboxes and query their individual value when the user confirms, that is, if unchecked turn option off, if checked then turn on and if indetermined (i.e. Null) leave that option to its previous state (on or off).

The problems start when the amount of options available is determined dynamically. Although it possible to add controls at run-time, at some point you will reach the practical limits of the Userform, not to mention not being able to craft a decent UI without too much effort.

I did not want to create controls at run-time -and honestly couldn’t- since I estimated the options to be 30 or more. Unfortunately, Excel and VBA do not offer the PictureBox available in VB that I would have used to create a scrollable container with the variable amount of checkboxes. Developing a custom UI ActiveX was also not an option (mainly because I did not want to distribute a DLL aside my application.

So  I investigated the possibility of using a ListView with checkboxes. Initially it looked nice, but soon I discovered that the checkboxes did not accept the triple state and also that they were pretty disappointing graphically (no 3D-look as with a regular checkbox control). Trying to play with the font and the foreground colour of the ListView item didn’t work out well because the of the highlight, inherited from Windows.

I finally decided to abandon the checkboxes in the ListView and use an ImageList control instead, containing the 3 icons for unchecked (Id 1), indetermined (Id 2) and checked (Id 3) state. I obtained the 3 icons using a Tristate checkbox as a template, the [Alt+PrtSc] button to capture the screen and Paint for saving them to Gif so that I could have a transparent background. All images are 16 x 16.

ImageList TriState Checkbox Images

ImageList TriState Checkbox Images

Seeing the result, I must admit that it outperforms my expectations:

ListView with TriState Checkboxes

ListView with TriState Checkboxes

To replicate this dialog, insert a Userform to your project. On this Userform add a ListView (using the Microsoft ListView Control, version 6.0) and an ImageList (using the Microsoft ImageList Control, version 6.0) populated as in the first image. Open the code section and paste the following code:

Option Explicit

' Used for the screen metrics (see function ConvertPixelsToTwips at the bottom).
'
Private Declare Function GetDC Lib "user32" (ByVal hWnd As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDC As Long, ByVal nIndex As Long) As Long
Private Declare Function ReleaseDC Lib "user32" (ByVal hWnd As Long, ByVal hDC As Long) As Long

Private mListItem As MSComctlLib.ListItem   ' Used for the Mouse clicks on the listview items

Private Sub ListView1_KeyUp(KeyCode As Integer, ByVal Shift As Integer)
On Error Resume Next
    ' Dehighlight whatever was highlighted.
    '
    ListView1.SelectedItem.Selected = False

    If (KeyCode = vbKeySpace) And (Shift = 0) _
    Then
        ' SpaceBar pressed all alone. Switch image.
        '
        With ListView1.SelectedItem
            If (.SmallIcon = 3) _
            Then .SmallIcon = 1 _
            Else .SmallIcon = (.SmallIcon + 1)
        End With
    End If

    ' Dehighlight whatever was highlighted.
    '
    ListView1.SelectedItem.Selected = False
End Sub

Private Sub ListView1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, _
                                ByVal x As stdole.OLE_XPOS_PIXELS, _
                                ByVal y As stdole.OLE_YPOS_PIXELS)
On Error Resume Next
    Dim wListItem As MSComctlLib.ListItem

    ' Dehighlight whatever was highlighted.
    '
    ListView1.SelectedItem.Selected = False

    ' Mark the item under the mouse pointer (used below).
    '
    ConvertPixelsToTwips x, y
    Set mListItem = ListView1.HitTest(x, y)
End Sub

Private Sub ListView1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, _
                               ByVal x As stdole.OLE_XPOS_PIXELS, _
                               ByVal y As stdole.OLE_YPOS_PIXELS)
On Error Resume Next
    Dim wListItem As MSComctlLib.ListItem

    ' Dehighlight whatever was highlighted.
    '
    ListView1.SelectedItem.Selected = False

    ' Nothing under the mouse? Forget about the previous item (see above).
    '
    ConvertPixelsToTwips x, y
    If ListView1.HitTest(x, y) Is Nothing Then Set mListItem = Nothing

    ' There was no previous item (see above) so we quit.
    '
    If mListItem Is Nothing Then Exit Sub

    ' Check if the item under the mouse pointer is the one we clicked on (see above).
    '
    If (mListItem.Text = ListView1.HitTest(x, y).Text) _
    Then
        ' Yes, switch image.
        '
        With mListItem
            If (.SmallIcon = 3) _
            Then .SmallIcon = 1 _
            Else .SmallIcon = (.SmallIcon + 1)
        End With
    Else
        ' No, de-select and forget.
        '
        mListItem.Selected = False
        Set mListItem = Nothing
    End If
End Sub

Private Sub UserForm_Initialize()
On Error Resume Next
    Dim wListItem As MSComctlLib.ListItem
    Dim i         As Long

    ' Initialise the ListView.
    '
    ListView1.HideColumnHeaders = False
    ListView1.ColumnHeaders.Add Text:="Filters", Width:=160
    ListView1.View = lvwReport
    ListView1.SmallIcons = ImageList1

    ' Load 3 elements in the ListView.
    '
    For i = 1 To 3
        Set wListItem = ListView1.ListItems.Add(Text:="Filter " & i)
        wListItem.SmallIcon = 1 ' Unchecked
        wListItem.Selected = False
    Next i
End Sub

' ------------------------------------------------------
' This function converts screen pixels (device dependent) to Twips (used a.o. by ListView).
' ------------------------------------------------------
'
Private Sub ConvertPixelsToTwips(ByRef x As stdole.OLE_XPOS_PIXELS, _
                                 ByRef y As stdole.OLE_YPOS_PIXELS)
On Error Resume Next
    Dim hDC            As Long
    Dim RetVal         As Long
    Dim TwipsPerPixelX As Long
    Dim TwipsPerPixelY As Long
    Const LOGPIXELSX = 88
    Const LOGPIXELSY = 90
    Const TWIPSPERINCH = 1440

    hDC = GetDC(0)
    TwipsPerPixelX = TWIPSPERINCH / GetDeviceCaps(hDC, LOGPIXELSX)
    TwipsPerPixelY = TWIPSPERINCH / GetDeviceCaps(hDC, LOGPIXELSY)
    RetVal = ReleaseDC(0, hDC)
    x = x * TwipsPerPixelX
    y = y * TwipsPerPixelY
End Sub

As you can notice, there is a conversion of the [X,Y] coordinates before running the HitTest() method. This conversion is necessary because Excel provides pixels in the mouse events, and the ListView requires Twips.

Enjoy,

Dutch.

Advertisements

2 Comments »

  1. hi i just try to trace your code and find out that my resault is diferent from you. i copy and past all of your codes in each related section. but at the end the only thing i see is a user form with a listview with 3 filters. image control is not there?

    Comment by ali — July 25, 2014 @ 3:51 pm | Reply

    • The Image control is not visible at run-time since it has no visual interface. The control is a container for images, and shown at design-time only. Does this answer your question?

      Comment by dutchgemini — July 28, 2014 @ 10:33 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: