Excel VBA ListView Control Examples
by Igor Katenov, the lead developer at 10Tec
The native ListView control from the OS Windows is a control allowing you to display items in various layouts. This versatile control can be used in Microsoft Office VBA. In this article you will find some Excel VBA ListView control examples with our analysis of their drawbacks and serious problems.
Adding the ListView control in VBA
Before you can add ListView control in VBA, you need to have the MS Windows common controls pack [MSCOMCTL.OCX] installed and registered in your system. If MSCOMCTL.OCX is registered properly, you can add the ListView control in Excel VBA using the Additional Controls dialog:
Pay attention to the location of MSCOMCTL.OCX in the Additional Controls dialog. This OCX is a 32-bit executable file, and it must be placed in the SysWoW64 system directory but not in System32 in 64-bit versions of Windows. For more info, read the following StackOverflow post:
ListView Common Control [mscomctl.ocx] not installed under Excel 2016 / Windows10
As you can also conclude after reading this post, the ListView control may be absent in the latest version of Microsoft Office and Windows. Fortunately for all us, we can still download the Microsoft Visual Basic 6.0 Common Controls redistributable package including ListView from this page on Microsofts website.
Many developers experienced problems with instantiating common controls like ListView on Excel worksheets after recent updates in the Windows and Office products even if the Windows common controls OCX was registered correctly. To avoid these issues in production code executing on the computers of your clients, you can try to Create ListView in VBA at Runtime. The essential part of the suggested code solution looks like the following:
' Create a ListView control in VBA code Dim objListView As ListView Set objListView = ThisWorkbook.Sheets[1].OLEObjects.Add[ _ ClassType:="MSComctlLib.ListViewCtrl.2", _ Link:=False, DisplayAsIcon:=False, _ Left:=50, Top:=50, Width:=200, Height:=100].Object ' Set ListView properties through the objListView reference objListView.LabelEdit = lvwAutomatic objListView.CheckBoxes = TrueOne important thing related to this code and any other code manipulating the ListView control in Excel VBA is the following. To use the specific ListView control types and properties in VBA code [like the ListView type itself, the LabelEdit and CheckBoxes properties, the lvwAutomatic named constant and the like], you need to add the reference to the MSComctlLib type library implemented in the same MSCOMCTL.OCX file to your Excel VBA project. If you do not do this, Excel VBA will highlight every member related to ListView with the message "Compile error: User-defined type not defined".
To add a reference to the MSComctlLib type library in your Excel VBA project, use the Tools\References dialog in Excel VBA:
If you do not find the "Microsoft Windows Common Controls 6.0 [SP6]" record in the list of available controls [which may be the case if you have never used common controls like ListView in Excel VBA], press the Browse button to select the MSCOMCTL.OCX file on your hard drive. Generally you will find it in the Windows System directory [System32 or SysWoW64 depending on the edition of your Windows].
Opening the References dialog in Excel VBA may fail with the "Error accessing the system registry" message in the latest versions of Windows like Windows 7 or Windows 10. If you see this, you need to relaunch the Microsoft Excel application with administrator rights to grant this application full access to the Windows registry for operations with COM type libraries.
Edit subitems in ListView in Excel VBA
The ListView control provides you with the in-place editing capability to edit item labels. However, if your ListView control works in the report [details] view to imitate a grid control, there is no built-in tool to edit ListView subitems.
You can find a lot of Visual Basic examples of how to implement editing for ListView subtitems in the Internet. All these solutions are based on the same idea: you need to add an additional text box control to your form containing the editable ListView control and place this extra text box over the required subitem when the user is going to edit it.
If try to go this way to implement editable ListView in Excel VBA, you will face two serious troubles related to this development environment. First, the vast majority of published solutions imply that you deal with pixel coordinates everywhere. But control positions and sizes in UserForms are measured in points, so you will need to convert them from points to pixels with helper functions based on the Windows API GetDeviceCaps call [see, for example, this link].
The other problem is that you cannot place the VBA TextBox control from the Microsoft Forms 2.0 package over the ListView control in VBA. Even if you can make this construction work with another text box control, think about reusing of this solution for all ListView controls in which you need subitems editing. You will duplicate the ListView editing infrastructure code and this external editor for all these ListView controls. Your Excel VBA solution will be bloated, and it will be very hard to support it.
Checkboxes in ListView subitems
"Add checkboxes in subitems of listview" is a popular Internet search query related to the ListView control. As a rule, people are searching for a solution to this problem when they use ListView in report mode and want to add checkboxes into several ListView columns.
Unfortunately, the best thing you can do using the native ListView features is to place checkboxes only into one column:
You can easily enable checkboxes for ListView items from VBA code by setting the CheckBoxes property of the ListView control to True, but this adds native checkbox controls only to the item labels in the very first column. If you need checkboxes only in one column displayed not on the first place, you can use a simple trick and move the columns with item labels to the required position. The screenshot with the ListView control on a UserForm above was captured after we had launched the form with the following initialization code, which is an example of how to add ListView subitems in Excel VBA:
However, this approach does not work if you need checkboxes in ListView subitems in several columns. An excellent idea how you can imitate real checkbox controls in ListView subitems was discussed on VBForums in this thread:
//www.vbforums.com/showthread.php?562763-How-to-add-checkboxes-in-subitem-of-listview
The idea is to use a picture of checkbox and display it in the required subitems. Fortunately, ListView allows us to easily add pictures to its subitems in the ListSubItems.Add method with its optional fourth parameter named ReportIcon. The checkbox image can be stored in the ImageList control from the aforementioned Windows common controls pack available for using on UserForms in Excel VBA as well. Below is one more Excel VBA code sample for the ListView control in which we show how to add images to ListView subitems:
With ListView1 .View = lvwReport .Gridlines = True .SmallIcons = ImageList1 With .ColumnHeaders .Clear .Add , , "Item", 40 .Add , , "Subitem-1", 55 .Add , , "Subitem-2", 55 .Add , , "Subitem-3", 55 .Add , , "Subitem-4", 55 End With Dim li As ListItem Set li = .ListItems.Add[, , "Item 1"] li.ListSubItems.Add , , "Subitem 1.1" li.ListSubItems.Add , , , 1 li.ListSubItems.Add , , , 1 li.ListSubItems.Add , , "Subitem 1.4" Set li = .ListItems.Add[, , "Item 2"] li.ListSubItems.Add , , "Subitem 2.1" li.ListSubItems.Add , , , 2 li.ListSubItems.Add , , , 1 li.ListSubItems.Add , , "Subitem 2.4" Set li = .ListItems.Add[, , "Item 3"] li.ListSubItems.Add , , "Subitem 3.1" li.ListSubItems.Add , , , 1 li.ListSubItems.Add , , , 2 li.ListSubItems.Add , , "Subitem 3.2" End WithThis is a neat solution to display Boolean values as checkboxes in ListView columns. But if you need to provide your users with the ability to change the statuses of the ListView checkboxes interactively by clicking them, you will need to write a lot of code to support this infrastructure and duplicate it together with the ImageList control storing the checked and unchecked checkbox images for every required ListView control in your Excel VBA project.
Conclusion
We examined several common usage examples for the ListView control in Excel VBA and saw that we may encounter troubles even in all these ordinary scenarios. We tried to provide solutions or workarounds if they are possible, but an alternative way could be using our iGrid ActiveX grid in Excel VBA for ListView in report view. You will never face any of the problems mentioned above with 10Tecs iGrid in Excel VBA and will get much more benefits, like built-in grouping/sorting, in-place editing of cells with the text and checkbox editors, fast flicker-free drawing code and dynamic cell formatting. Explore the product pages and articles on this website to find out more about 10Tecs Excel VBA ListView control alternative. You can start reading from the following article:
Excel VBA Grid Control»