Building a Custom Listbox in Excel with VBA

While the Listbox Form and ActiveX control can suit the needs of many, they lack many options that provide full interactivity and customization. A custom VBA solution will give you full control of how listboxes look, and how users interact with them.

DifficultyAdvanced
Time Est.60-90 Minutes
VBA/MacrosYes
Template Files2016-10-23-custom-listbox-with-vba-template
Solution Files2016-10-23-custom-listbox-with-vba-solution

Spreadsheet Design Exercise 9

This exercise of the Spreadsheet Design series, continues the focus on Data Management. The steps below contain a large number of code snippets. If you would like to jump to the final consolidated code, click here.

Continuing from the previous post on custom listboxes with Excel formulas, today’s post will provide an overview on how to construct a similar listbox using VBA. The result will be similar to the non-VBA solution, but will operate much more smoothly and have the potential for many other added features (some of which will be discussed in later posts).

Step 1: Set up workbook with dataset.

This exercise will use a variation of the solution from the Adding New Data to Datasets in Excel post, which can be downloaded here: 2016-10-23-custom-listbox-with-vba-template. The layout of the form has been changed to a horizontal format. This does not change any functional element; it will simply better visually incorporate the data input and data display features in the final solution. Column widths in the walk-through images (below) have been shrunk from their normal size to better fit on this blog.

Step 2: Add scrollbar to worksheet.

Add an ActiveX Scrollbar to the worksheet. Make sure that the developer tab is active, then click (1) Developer > (2) Insert, then (3) select the Scrollbar object from the ActiveX Controls menu (do not to select the Form Control version).

The ActiveX Scrollbar control can be found through the developer menu.
The ActiveX Scrollbar control can be found through the developer menu.

The cursor should now turn into a crosshair. Move the cursor to the top left corner of cell J7 and click – the scrollbar should appear. Resize the scrollbar so it’s on the left side of column J, flush with the border of column I, between row 7 and row 15.

Place the scrollbar in the top left corner of cell J7.
Place the scrollbar in the top left corner of cell J7.

Step 3: Add listbox anchor and data column references.

This approach will require a few column references with named ranges. While it is possible to reference the cell address directly, providing named ranges will help cope with worksheet layout changes and make VBA code more intuitive. Start by creating an anchor cell for the custom listbox, named “Listbox_Anchor” in cell B7.

Name cell B7 Listbox_Anchor.
Name cell B7 as Listbox_Anchor.

The following has already been set up in the template provided, but when you are working on your own projects, you want to make sure to provide a label to each header item in the dataset. This allows the code to reference columns and will prevent errors if new columns are ever added to the dataset.

Create named columns in the dataset - this will tell the listbox which columns to pull.
Name ranges have already been added to the dataset headers in the template, but make sure you remember to name datarange headings when using your own datasets.

Step 4: Create scrollbar change event.

Any interaction with the scrollbar will pass a scrollbar change event through a VBA worksheet object. You can easily create this by double clicking on the scrollbar after it is positioned. After you double click, the VBA worksheet object will open with the scrollbar change event subroutine

.
Directly under the Private Sub Scrollbar1_Change() line, type “Call Scroll_Action”. This will invoke a subroutine that will be created in the next step. The code in the worksheet object should look as follows:

Step 5: Planning VBA Scripts.

The coding for this exercise is somewhat complex, requiring two module subroutines (in addition to the previously created worksheet object subroutine), and one small modification to the Add_Stock_Purchase subroutine created in the Adding New Data to Datasets in Excel exercise. The following steps will break down each subroutine into steps, then break it down further into snippets explaining what each process does.
There are two core processes that need to be address: (1) the reprinting of data when the scrollbar is used and (2) the reprinting of data and the increase of the maximum scroll value when new data is added. To have an overall better understanding, here’s a narrative summary of the processes completed. Each of these processes will be broken out into a snippet.
Scrollbar Actions

  1. Determine scrollbar values.
  2. Collect data from dataset into array to prepare for listbox print
  3. Assign colors to listbox display rows.
  4. Print array on listbox display.

Data Input Actions

  1. Add_Stock_Purchase subroutine invokes a new Set_Scroll_Max subroutine and the existing Scroll_Action subroutine.
  2. New Set_Scroll_Max subroutine adjusts scrollbar maximum.

As always, the full code without interruption can be accessed by clicking here.

Step 6: Scrollbar actions.

Process 1: Set scrollbar values.

The subroutine contains the parameter scrollValue. As previously set up, in step 4, interacting with the scrollbar will pass the value which tells the conditional to reassign scrollValue to the actual value of the ScrollBar1 object. Later in this exercise, will add a call to the Scroll_Action subroutine after new data is added, creating a professional touch where the scrollbar returns to the top of the listbox where the new data exists.

Process 2: Collect data from dataset into array to prepare for listbox print.

This snippet builds a 2-dementional array containing all the values from the dataset that will be printed in the listbox display. The For loop variable i starts at 2 (the first data row in the dataset) plus the scrollValue (the current value of the scrollbar), the sum of which reflects the scrollbars “position” within the dataset. The For loop goes through 10 plus the scrollValue, providing 9 iterations to fill the 9 listbox display rows.

Process 3: Assign colors to listbox display rows.

Before closing the For loop, the script assigns alternating colors to the rows in the listbox display. This is done using the Mod operator, which tests whether each scrollValue position (i) in the dataset is even or odd. The conditional assigns an RGB value to the Long variable rowBackColor, then prints it on the range calculated below.

Process 4: Print array on listbox display.

The final step for the scrollbar action is to print the array in the listbox display. Using the listboxAnchor range variable (which is defined by the “Listbox_Anchor” named range), the Resize method is used to calculate the print area based on the upper bounds of the 2-dimensional array. The range is then assigned the value of the array.

Step 7: Adjusting scrollbar max when new data posts.

Our last step within the coding module is specific to the template supplied with this exercise, but a similar procedure should be followed on your own projects, appending these calls to any subroutine you use to add, remove, or modify data from the dataset.

Process 5: Add_Stock_Purchase subroutine invokes a new Set_Scroll_Max subroutine and the existing Scroll_Action subroutine.

Add the following two lines of code to the bottom of the Add_Stock_Purchase subroutine, just before the End Sub line.

You can see these two lines highlighted in the context of the entire subroutine code, here.
This process invokes the Scroll_Action subroutine to reset the listbox with any new values added. If this is omitted, the new data will appear the next time the scrollbar is used, which would be considered a lag in responsiveness of the application.

Process 6: New Set_Scroll_Max subroutine adjusts scrollbar maximum.

The default value assigned to a new scrollbar is 32767. If the default is not changed, the scroll box will not visibly scroll for small datasets (and fast scrolling cannot be used), and the scrollbar will continue to show blank rows once it’s passed the dataset range. To resolve this, the following script can recalculate the size of the data and adjust the scrollbar maximum value every time a new data row is added.

The script cycles through the rows of the dataset with a Do Until loop, then assigns the value to ScrollBar1, after adjusting for the size of the listbox and zero values (11, in this case).

Step 8: Final formatting.

Now that coding is complete, you can test the scrollbar, then add the final formatting touches. For the full effect, it is suggested to include a border and line up the data with headers. Make sure to format the display data to be identical to the formats of the data input form. Note that the scrollbar maximum will remain at its default (32767) value until you add new data.

Appearance of the custom listbox without any final formatting.
Appearance of the custom listbox without any final formatting.
Exercise 7 completed solution with listbox formatting.
Exercise 7 completed solution with listbox formatting.

Full VBA Script

Note that the following is a repeat of the subroutine Add_Stock_Purchase, which was included in the template provided with this exercise. Step 7, Process 5 requested that two additional lines of code be added to the bottom. These lines new lines (104 and 105) are highlighted, and the entire subroutine code is printed to provide context.

Video and Screen Captures

Exercise 7 final solution.
Exercise 7 final solution.

Download Solution: 2016-10-23-custom-listbox-with-vba-solution

Other Points

  • There are several other features which can be implemented along with (and within!) a custom listbox. This includes validation indicators, integrated forms, and checkboxes.
  • While the alternating color change is not required, it does help aid in the scrolling effect. Omitting the alternating colors may give the user a sense of numbers randomly changing.
  • Unlike the non-VBA approach, this approach does not invoke Excel’s animation features which interfered with the visual scroll effect.
  • Excel does provide it’s own ActiveX ListBox control. This will be discussed further in a future post. While the ActiveX control is easier to implement, it’s quite restrictive in terms of formatting and other features.

Drawbacks/Bugs

  • Excel’s ActiveX controls can be a bit unpredictable, specifically when they are included in hidden, then unhidden ranges. There are also issues when monitor resolution changes, so be careful when plugging into a presentation projector.
  • Some users may have the urge to update data directly on the listbox display, but keep in mind that it’s just a visual image of the dataset. A good approach is to lock up the cells to prevent any confusion.

Leave a Reply