ListBox with Excel VBA

The ListBox control is used to store a list of values.
From the list, the user can select one or more values, which are then used in the program code.

Note that, during design, a list visually looks similar to a text box. Typically, a list item is selected with a single click. A double-click on a list item can be used to trigger certain actions in the program related to that item.

The Change event is generated whenever the selected item changes.

Commonly Used Properties and Methods of ListBox

Table. ListBox Properties

• fmMultiSelectSingle (only one item can be selected),
• fmMultiSelectMulti (multiple selection allowed using clicks or ),
• fmMultiSelectExtended (multiple selection allowed using for consecutive items). |
  | Selected | Boolean property returning True if an item is selected, False otherwise. Used when MultiSelect is set to Multi or Extended. |
  | ColumnWidths | Sets the width of list columns. |
  | ColumnHeads | Boolean property defining whether column headers are displayed. |
  | ListStyle | Sets how selected items are highlighted. Possible values:
• fmListStylePlain (highlighted by color),
• fmListStyleOption (a checkbox is placed next to each item). |
  | MatchEntry | Displays the first matching list item while typing. Possible values:
• fmMatchEntryNone (disabled),
• fmMatchEntryFirstLetter (match by first letter; list preferably sorted alphabetically),
• fmMatchEntryComplete (match by full typed name). |
  | BoundColumn | Defines the data returned by the Value property. Possible values:
• 0 → returns the row index (same as ListIndex).
• 1..n → returns the item from the selected row in the specified column. |

Table. ListBox Methods

ListBox Scenarios

As an example of using lists, let’s return to the scenario of calculating the sum a + b + c for different sets of values. This time, instead of buttons, the scenario will be managed with a ListBox.

Steps:

• On the worksheet, allocate cells B2, B3, B4 for the variables a, b, c.
• In cell B5, enter the formula:
• =SUM(B2:B4)
• In range E3:G3, enter the first set of values for a, b, c, and in cell D3 enter its scenario name.
• In range E4:G4, enter the second set of values for a, b, c, and in cell D4 enter its scenario name.
• Create a ListBox and, using the Properties window, set the property values as shown in next Table.

Table. Property values set in the Properties window

In the Sheet1 module, type the necessary code.

The ListFillRange property fills the list with scenario names taken from the specified range.
The Change event procedure of the ListBox determines the index of the selected item via ListIndex.
This index identifies the range containing the chosen set of values, which is then copied to the clipboard and pasted (transposed) into B2:B4 for calculation.

Worksheet Cell Protection

In projects with a user interface, it is often necessary to maintain data integrity: the user should only be allowed to perform permitted actions, without disrupting the structure. For example, they should only be allowed to enter values in specific cells, while input in all other cells should be programmatically blocked.

This effect is achieved using the Protect method, which applies protection to the worksheet with the parameter UserInterfaceOnly = True.

• This prevents the user from editing locked cells.
• At the same time, macros are still able to edit them programmatically.
• The Locked property of cells defines whether the cell is locked or editable.

A simple project demonstrates the Protect method and the Locked property.

Scenario:

• The task is to calculate either the sum or the difference of two numbers entered into cells B1 and B2.
• The operation is selected by the user from a ListBox.
• Only cells B1 and B2 are unlocked for input.
• The operation name and the result formula are programmatically written to cells A3 and B3.

Printing Control Elements

In a printed report, sometimes you need to show control elements, while in other cases you may not (if they serve only as helper tools).

The PrintObject property of controls determines whether the control is printed.

• If True, the object will appear in the printout.
• If False, it will not.

To demonstrate this property, modify the previous project:

• Add two buttons and a checkbox.
• If the checkbox Print Controls is checked, the elements and list will appear in the printout.
• If unchecked, they will not.
• The Print button starts printing.
• The Preview button shows a print preview of the worksheet.

Table. Property values set in the Properties window