Building a Custom Listbox in Excel with Formulas

As discussed in previous posts, separating users from raw datasets is a cornerstone of excellent spreadsheet design. Today’s post provides a non-VBA solution to displaying data to users through a dynamic custom listbox.

Difficulty Advanced
Time Est. 45-60 Minutes
VBA/Macros No
Template Files 2016-10-22-custom-listbox-no-vba-template
Solution Files 2016-10-22-custom-listbox-no-vba-solution

Spreadsheet Design Exercise 8

This exercise for the Spreadsheet Design series, continues the focus on Data Management. VBA is not required to complete this exercise, but the template provided does contain VBA.

While there are obvious arguments for preventing users from being able to write and execute directly in the raw datasets, there is not as much of a convincing argument for reading in raw datasets. If necessary, you could simply password protect a dataset spreadsheet and let users access it for viewing via a tab.

However, the goal of this series is to make your spreadsheet act and behave like a regular desktop application or web-based software interface. Unless you’re an IT support person in a corporate accounting department, you likely do not have any read access directly in the database management system.

This exercise will provide you with an effective technique for displaying data on a dashboard or form page. The approach involves using a scrollbar form control to mimic the feel of listbox. While Excel does have it’s own listbox control, creating a custom listbox gives you more flexibility in terms of design. This approach does not require VBA. A VBA approach will be provided in the next post.

Step 1: Set up spreadsheet with dataset.

This exercise will be using the solution from the previous post, which can be found here: 2016-10-22-custom-listbox-no-vba-template. If you would like, you may also use your own dataset, but keep in mind that the specifics of the exercise will follow the template.

Step 2: Set up worksheet operations.

The initial setup will require us to add two new value fields to the worksheet Operations tab. The template currently contains two worksheet operations values (columns A and B) from the Adding New Data to Datasets in Excel post. For this step, add two new values: Ops_Scroll_Num and Ops_Scroll_Even. Continuing with previous practices, row 1 should contain the value referencing the name of the cell below, as cell names are invisible.

Add named ranges to the Operations tab.
Add named ranges to the Operations tab.

For now, leave the Ops_Scroll_Num named range blank. In the Ops_Scroll_Even named range, enter the following formula:

=ISEVEN(Ops_Scroll_Num)

The value of TRUE will appear.

Step 3: Create scrollbar and value assignments.

Initial setup work in the worksheet Operations tab is now complete, and you can now return to the interface tab. This step will add a scrollbar to the worksheet. You can add a scrollbar by clicking on the (1) Developer > (2) Insert, then click on the (3) scroll bar image. In this exercise, make sure to use the scroll bar under Form Controls (not ActiveX Controls)

Add a scrollbar Form Control to the worksheet through the Developer menu.
Add a scrollbar Form Control to the worksheet through the Developer menu.

The cursor should now turn into a crosshair. Move the cursor to the top left corner of cell J20 and click – the scrollbar should appear. Resize the scrollbar so it in column J, flush with the border of column I, between row 20 and row 28.

Resize the scrollbar so it is flush with the border of column I.
Resize the scrollbar so it is flush with the border of column I.

Once properly placed, right click on the scrollbar and click Format Control… The following dialogue box will appear.

The Format Control dialogue box allows you to modify basic properties for a scrollbar form control
The Format Control dialogue box allows you to modify basic properties for a scrollbar form control

Default values will need to be updated. The first 3 (Current value, Minimum value, Maximum value) deal with the template’s unique numbering, allowing the custom listbox to know where to look for data. With these settings, the scrollbar can scroll for up to 100 rows.

The Page change value specifies how many rows the scrollbar will jump during a fast scroll. Given that our custom listbox will be 9 rows, reduce the value to 9.

The Cell link value identifies a cell or named range in which the scrollbar value will be printed every time the scrollbar is triggered. Enter the value “Ops_Scroll_Num”, as this is the named range we placed on the Operations sheet to store this value.

Step 4: Setup listbox key.

With the scrollbar properly set up, you can now begin constructing the listbox content. Initially, we want to include a “Purchase ID” column which will cycle through all of the “Data_Purchase_ID” values in the dataset. The trick is that this field is driven by the scrollbar, even though it will appear to the user to be actual data.

In cell B20, enter the following formula:

=Ops_Scroll_Num

In cell B21, enter the following formula:

=B20+1

Drag the contents of cell B21 down to B28. The final result should look as follows:

Click and drag the contents of cell B21 down to cell B28.
Click and drag the contents of cell B21 down to cell B28.

When press then down or up arrows of the scrollbar, the numbers should now increase or decrease.

Step 5: Setup listbox formulas.

The rest of the listbox can be constructed with one VLOOKUP function. The VLOOKUP function simply grabs the “Purchase ID” value in column B, then searches the “Data_Purchase_ID” column in the Data tab to pull the remaining columns. As clicking the scrollbar increases or decreases the “Purchase ID” values in column B, the listbox gives the user the appearance of scrolling through data.

The VLOOKUP will be nested within an IFERROR function, ensuring that no #N/A values are returned when the listbox gets to the end of the data. This step isn’t necessary, but adds a professional touch.

In cell C20, enter the following formula:

=IFERROR(VLOOKUP($B20,Data!$A:B,COLUMN(Data!B:B),FALSE),””)

This formula has been set up so you can drag it all the way to the right, to Column I, then the entire row down to row 28. Formatting will not transfer from the Data tab, so it’s a good practice to update formatting in each cell in the first row before dragging down.

Drag contents in row 20 across to column I, format each field, then drag first row down to row 28.
Drag contents in row 20 across to column I, format each field, then drag first row down to row 28.

The general functionality of the custom listbox is now set. When you click on the up or down arrows of the scrollbar, you will see values change. However, the effect does not seem to work well at this point. As opposed to seeming like values are moving up and down, it seems as if numbers are changing randomly. If you’re using a version of excel with animation change effects, it looks even worse. The next two steps will introduce formatting to help mitigate this issue.

Step 6: General custom listbox formatting.

You can now format your listbox with a header row and border. Border formatting preference will not impact the listbox’s functionality. Make the background of the listbox contents white..

Basic border formatting instructions at TechOnTheNet: https://www.techonthenet.com/excel/cells/border2016.php

The custom listbox control without background formatting.
The listbox is almost complete.

Step 7: Row scroll effect with conditional formatting.

If you recall in step 2, we set up a named range within the Operations tab called Ops_Scroll_Even with the formula =ISEVEN(Ops_Scroll_Num). This formula returns a value of TRUE if the value of the scrollbar is even. Now, we’ll use this to alternate colors on the custom listbox, providing a true scrolling effect.

With this approach, we want to set conditional formatting to highlight even rows when the Ops_Scrol_Even value is TRUE, and odd rows when the value is FALSE.

Start by (1) highlighting the entire first of the listbox (cells C20:I20), then click on (2) Home > (3) Conditional Formatting > (4) New Rule.

Create a new rule through the conditional formatting menu.
Create a new rule through the conditional formatting menu.

A New Formatting Rule dialogue box will appear. In the Select a Rule Type listbox, (5) choose the last value: Use a formula to determine which cells to format. In the Format values where this formula is true field, (6) type “=Ops_Scroll_Even”. Then (7) click on format, which will bring up a new Format Cells dialogue box to choose formatting properties. (8) Select Fill, then (9) select a color. This example will use light blue, but you can select any color you want. (A) press OK in the Format Cells dialogue box, then (B) press OK in the New Formatting Rule dialogue box.

The New Formatting Rule dialogue box.
The New Formatting Rule dialogue box.

 

The Format Cells dialogue box.
The Format Cells dialogue box.

To test the effect, use the scroll form. The color should alternate between blue and white as you scroll.

Next, a similar conditional format should be set for row 2, but it should only be blue when Ops_Scroll_Even is returning false. We can accomplish this by following the previous instructions, while making the following changes:

  • In instruction 1, highlight the 2nd row of the listbox range (cells C21:I21)
  • In instruction 5, use “=Ops_Scroll_Even=FALSE” as the formula.

Now scrolling with the scrollbar should make the color of rows 1 and 2 alternate. Just one more easy thing to do before we have a fully functional custom listbox: copy the formatting to the remaining 7 rows.

This can accomplished in one motion by doing the following. (C) Select and copy the first two rows (cells B20:I21), (D) select the remaining 7 rows (cells B22:I28), then select (E) Home > (F) Paste (make sure to select the down arrow), then (F) Paste Formats under Other Paste Options. The conditional formatting should now be in effect throughout the entire listbox. The top border may have also copied down the listbox; this can be removed through the Format Cells dialogue.

Copy first two rows of listbox and paste formats to remaining rows.
Copy first two rows of listbox and paste formats to remaining rows.

Screen Capture

The final custom listbox solution should look like this. Note that the column widths have been shrunk to better fit in this blog.
The final custom listbox solution should look like this. Note that the column widths have been shrunk to better fit in this blog.

Download Solution: 2016-10-22-custom-listbox-no-vba-solution

Other Notes

  • You can include any number of rows as you’d like. However, it rarely makes sense to include more rows than can be shown in the screen, and also keep users with lower resolutions in mind.
  • Excel’s built in animation change effects (new in the 2015 version) interfere with the scrollbox effect. It is possible to turn this off, but it will turn it off for all other workbooks as well.
  • Excel does have its own listbox form control, but it has quite a few bugs and limitations. This form control will be discussed in a later post.
  • Incorporating VBA with scrollboxes leads to limitless possibilities. For example, you can create register effects by incorporate input/modification forms—with validation controls—directly on the listbox. While the next post will discuss how to create a custom listbox with VBA, a future post will discuss these advanced register features.

Bugs/Issues

  • Excel’s default on change animation feature interferes with the visual scrolling effect. This issue does not occur with the VBA scrolling solution.
  • Excel form controls can sometimes be unpredictable and buggy. Specifically, the form controls sometimes disappear (shrink to a height of zero) if they are part of any hidden ranges. This can sometimes cause a conflict with the VBA navigation solution.

Leave a Reply