How To Make An Excel VBA Listbox Act Like A Grid

The Problem

You’re writing an Excel VBA Front end to some sort of system, it could be an Access, Sql Server or Oracle database, or some text files you’re consolidating from third party data sources.

You want to incorporate a Grid into your vba form so that it behaves similar to a Microsoft Access form and sub form relationship or any other slick third party applications that you’re used to.

Maybe you’d like your form to do something like this…

However the above grid functionality (programatically display listbox as grid, with automatic column headers) is not intuitively native in Excel VBA.

The Solution / Workaround

A listbox will give a grid style layout when it’s rowsource property is set to link directly to a spreadsheet range (rowsource range address must exclude the column headers for the labels to show as headers in the grid), and it’s columnheader property is set to True.

Meaning the spreadsheet range has a list style layout with the top row being the list headers and the rest being data (refer to figure 2 below), then link to this range excluding the headers.

But I hear you say "I don’t want to link to a spreadsheet range I want to get data from Access or Oracle and make selections on my vba form!"

Don’t worry I’ve got your back here, you create a hidden sheet for each listbox on the form (you could just have one hidden sheet for all your listboxes, I just think it’s easier to organise this way) you write the returned data from you sql query to that range.

Before you complain, that this all seems like too much work…

It actually only requires a few lines of code…

03 ' Partial Code snippet	
04 RangeObject.CopyFromRecordset(RecordsetName) ' Recordset name being the data returned from your query.

The above code will write the contents of the recordset object to the sheet address refered to by said range object, e.g. if myRange = A4 then the recordset will be written to the sheet starting with cell A4 as the top left corner.

Get the coordinates of your new "splatted" range and apply these to the rowsource of the listbox.

So in the case below (Figure 2) the data could have been returned from a Sql Server query to cell A4 of the hidden worksheet (vba code would clear this spreadsheet data prior to writing new data from Sql Server).

The above image, shows a range within a hidden Excel worksheet / tab, this could be the result of the CopyFromRecordset() method in the above code snippit.

In the listbox above (Figure 1), the process I’ve just described drives the initial loading of the form, which you can see in the dynamic image at top when the "Clear Filter" button is clicked.

It works really well, trust me…

In the example above I’ve used some static data in a hidden sheet, combined with vba driving the advanced filter to create sublists on a new sheet based on the city criteria.

However in the past I’ve created hirearchical Excel vba forms for clients with many levels of filtering i.e. subforms based on subforms, each filter involving a sql query against for example a backend sql server database to drive each section of the form, and … it worked like a charm!

The Details

Create a form similar to the example below, name the listbox on the left lstCities and the one to the right, lstData

This form displays two datasets… the contacts based in a particular city (lstData), and a unique list of cities extracted from that data using the Excel vba advanced filter (lstCities). It’s all done programatically in vba. Both list objects on the form are standard Excel listboxes, the listbox on the right however is behaving like a grid with automatic labeling of column names.

When the form loads the initial datasets are loaded, list of cities and the complete dataset for the grid.

lstCities is populated via an array containing the unique city list, this was done by using an Excel Advanced Filter behind the scenes and assigning the result to the vCities array variable.

03 ' vCities below is a variant array	
04 lstCities.List = vCities

The following code will populate the grid, you can download the spreadsheet that accompanies this article to see how to make the form subform element work i.e. click on a city and only contacts for that city appear in the lstData Grid.

01  Option Explicit
02
03  Sub LoadAllDataToDataList()
04    Dim rngData As Range   ' Create range variable
05    
06    ' Get a reference to the sheet range, this could be the external data
07    Set rngData = shtDatasource.Range("A4").CurrentRegion
08    
09    ' Switch on colum headers this is what makes it act as a grid
10    lstData.ColumnHeads = True
11    
12    ' Match the number of columns in the grid to the columns in the data range
13    lstData.ColumnCount = rngData.Columns.Count
14      
15    ' Offset the range address by one and column heads will come in automatically
16    Set rngData = rngData.Resize(rngData.Rows.Count - 1).Offset(1)
17    
18    ' Set the rowsource property of the grid to the datarange address, excluding the header address
19    lstData.RowSource = rngData.Parent.Name & "!" & rngData.Address
20    
21    ' Set the column widths of the data, note that first column (ID) is hidden
22    lstData.ColumnWidths = "0;80;70;115;70;20"  
23  End Sub

The code above is written inside the form and and is called by the the UserForm_Initialize() method of the the form.

That being the code that runs automatically when the form loads.

That’s pretty much it, there is more code to make the form sub form aspect work such as using the on_change event of the lstCities but I didn’t want to muddy the water by adding too much detail here, you can see all the code for this article in the accompanying spreadsheet.