Back
Excel

Simplifying data entry with a list box and VBA code

To plan which topics to write during the next three months, our Office documentation team needed an easy way to enter values in an Excel worksheet limited only to values in a list. However, we couldn’t use Excel’s data validation feature because it only lets you select one item per cell.

The solution? Adding a multi-selection list box to the worksheet. But to allow multiple values, we would also need to use the ActiveX control list box. And, more importantly, we’d have to hook it up to some VBA code to make it work.

Afterwards, I reflected on what I’d built and thought: A lot of users ask about list boxes, data validation, and macros (or VBA), so why not combine all three topics into one post?

First things first…to do any VBA programming in Excel, you need the Developer tab visible on the ribbon so you can get to the VBA and the ActiveX control commands. To get the Developer tab, bring up the Excel Options dialog box, click Customize Ribbon, and select the Developer check box.

 

Excel Options dialog box

 

Now that the Developer is available, let’s zoom in on it for a closer look. Here in the Controls group, you’ll see an Insert button. Click it, and you get this Form Controls menu.

 

Inserting a list box ActiveX control

Now here’s what I did next. Under ActiveX Controls, I click the List Box button, click anywhere in the worksheet, and drag and release to create my list box. I can select it and move it wherever I want . Now, on to changing a few settings for the list box. With the list box selected, I right-click and then click Properties on the menu, which brings up the Properties box for the list box.

I want users to be able to make multiple selections. By default, that option is turned off. It’s controlled by the MultiSelect property, so I choose setting 1, which is fmMultiSelectMulti. I want little check boxes next to the values in the list, so I set the ListStyle property to 1, which is fmListStyleOption.

Now, to make the list box worth using, I need to attach the value choices to the list box. That’s a list of values in a range of cells, and it needs to go in the ListFillRange property. In the box for this property, I type Validate!$F$2:$F13, which is a reference to a range of cells on another worksheet named “Validate.”

 

ListFillRange property for the List Box control

 

That’s pretty much what I need to get started. Now, on to writing and testing my VBA code.

I hook up VBA code to the list box by right-clicking the list box, and then I choose View Code. This starts the Visual Basic Editor (the VBE), where I can start programming.

The Visual Basic Editor, ready for action!

 

I click the dropdown list that contains Click, and then select KeyPress. That creates a subprocedure for me called ListBox1_KeyPress. I can write my lines of code right there.

 

ListBox1_Keypress subprocedure in the VBE

 

Now before I write any actual code, I make a list of things that I think I’ll need to do to make this all work. In the Visual Basic Editor, between the lines “Private Sub ListBox1_Keypress(ByVal KeyAscii As MSForms.ReturnInteger)” and “End Sub,” I write down my ideas with an apostrophe at the start of each line, which turns them into comments, like this:

 

Some comments I wrote as an outline

 

And here’s the VBA code for the main subroutine  (there are two subroutines, ListBox1_KeyPress and ClearBoxSelections). Below the code, I’ll explain what some of the lines do.

Sub ListBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If KeyAscii <> 13 Then
  ClearBoxSelections
  Exit Sub
End If

Dim intActiveCol As Integer
Dim strWrongCol As String
Dim intAppCodeOffset As Integer
Dim strAppCodeVal As String
Dim strActiveColTitle
Dim selRange As Range

strWrongCol = "Please select a cell in the Applications column, and try again."

'Get active cell column; is cell address in correct column ("Applications")?

intActiveCol = ActiveCell.Column
strActiveColTitle = Sheets("Planning").Range("A1").Offset(0, intActiveCol - 1).Value

If Not strActiveColTitle = "Applications" Then
  MsgBox strWrongCol
  ClearBoxSelections
  ActiveCell.Select
  Exit Sub
End If

'If not correct column, show msgbox "...select a cell in the Application column."

If Not strActiveColTitle = "Applications" Then
  MsgBox strWrongCol
  ClearBoxSelections
  ActiveCell.Select
  Exit Sub
End If

Set selRange = Selection

For i = 0 To ListBox1.ListCount - 1
  If ListBox1.Selected(i) = True Then
    If strApps = "" Then
      strApps = ListBox1.List(i)
      intAppCodeOffset = i
      strAppCodeVal = Worksheets("Validate").Range("G2").Offset(i, 0).Value
    Else
      strApps = strApps & ";#" & ListBox1.List(i)
      intAppCodeOffset = i
      strAppCodeVal = strAppCodeVal & ";#" & Worksheets("Validate").Range("G2").Offset(i, 0).Value
    End If
  End If
Next

If strApps = "" Then
  MsgBox "Select at least one application."
  ActiveCell.Select
  Exit Sub
End If

Set selRange = selRange.Offset(0, -1)

With selRange
  selRange.Value = strAppCodeVal
End With

Set selRange = selRange.Offset(0, 1)

'Clear all of the box selections from the list box

ClearBoxSelections

ActiveCell.Select

End Sub

 

“KeyAscii <> 13″ is a test to see if the user has pressed the Enter key (the Enter key sends a “13” value to the computer). If not, the code clears the selected check box and exits. 

This following section tests to see if the user selected a cell in the appropriate column (the column with “Applications” in row 1). I use the Range property with the Offset method to get the value of the cell in the first row of the current column, like this:

 
Sheets("Planning").Range("A1").Offset(0, intActiveCol -1).Value

 

Here’s a closer look at  the code for that section:

 
intActiveCol = ActiveCell.Column
strActiveColTitle = Sheets("Planning").Range("A1").Offset(0, intActiveCol - 1).Value

If Not strActiveColTitle = "Applications" Then
  MsgBox strWrongCol
  ClearBoxSelections
  ActiveCell.Select
  Exit Sub
End If

 

If the currently selected cell is in the wrong column (that is, not the “Applications” column), a message appears from Excel (“Please select a cell in the Applications column, and try again.”), all the selected check boxes in the list box are cleared, and the user needs to start over. Here’s that code:


If Not strActiveColTitle = "Applications" Then
  MsgBox strWrongCol
  ClearBoxSelections
  ActiveCell.Select
  Exit Sub
End If

 

The code uses a message box to display the “Please select a cell in the Applications column, and try again.” text I defined as the variable strWrongCol. Then, my second subroutine named ClearBoxSelections runs, which clears all selected check boxes, re-selects the active cell and, finally, exits the main subroutine. In other words, the code helps things “fail gracefully.”

If the selected cell is in the correct column, it’s a go — the code runs through the list box and acts on each selected value (that is, each selected check box). For each selected item (check box) in the list, the code sends Excel to the “Validate” worksheet where the range of values is stored, and finds the match in that range for the selected value in the list box. The values are stored in the range alphabetically, so “Access 2010″ is the first item (0, not 1). The beginning of the loop is “0,” so the code stops at the first value in the cell range, “Access 2010,” and assigns that value to the variable strApps.

Then, the code offsets (moves) one cell to the column on its right (we’re still on the “Validate” worksheet) and gets the value from that column, which is 1574. It assigns that value to the variable strAppCodeVal. The code then looks for the next selected value in the list box, and if it finds one, it adds the string “;#” as a separator, and then adds the next value it finds that matches the next selected check box. In this case, it finds “Excel 2010″  in the cell range and writes that value to the strApps variable. The code then does the offset action again, finding the numeric code 1576, and writes that to the strAppCodeVal variable, of course, writing the separator “#;” first. All this repeats until Excel finds no more selected check boxes. Since my example had three check boxes seleted (Access 2010, Excel 2010, and Word 2010), there will be three App values and three App codes in each variable:

strApps = Access 2010;#Excel 2010;#Word 2010

strAppCodeVal = 1574;#1576;#1691

If no values were selected in the list box, the subroutine displays the message “Select at least one application” and exits (after re-selecting the active cell).

 

Set selRange = Selection
For i = 0 To ListBox1.ListCount - 1
  If ListBox1.Selected(i) = True Then
    If strApps = "" Then
      strApps = ListBox1.List(i)
      intAppCodeOffset = i
      strAppCodeVal = Worksheets("Validate").Range("G2").Offset(i, 0).Value
    Else
      strApps = strApps & ";#" & ListBox1.List(i)
      intAppCodeOffset = i
      strAppCodeVal = strAppCodeVal & ";#" & Worksheets("Validate").Range("G2").Offset(i, 0).Value
    End If
  End If
Next

If strApps = "" Then
  MsgBox "Select at least one application."
  ActiveCell.Select
  Exit Sub
End If

 

 

Now, we’re ready to write the values to the active cell (the top cell in the selected range — suppose you’ve selected cells G10:G20 — the “active cell” is G10, and the cell to its left in the same row — and any other selected cells (the selected cells are represented by “selRange” in the code below).  I use the Value method to write the value of the strApps variable to the active cell, use the Offset method again to move one column to the left and then use the Value method to write the strAppCodeVal value to that cell. The code then reselects the range that the user originally selected (the “Set selRange = selRange.Offset(0, 1) line).

With selRange
  selRange.Value = strApps
End With

Set selRange = selRange.Offset(0, -1)

With selRange
  selRange.Value = strAppCodeVal
End With

Set selRange = selRange.Offset(0, 1)

Then the second subroutine, named ClearBoxSelections, runs. It clears the selections from the list box.

Sub ClearBoxSelections()

For i = 0 to ListBox1.ListCount -1
  ListBox1.Selected(i) = False
Next ()

End Sub

This line selects the original active cell.

ActiveCell.Select

And here’s what an Application ID and Applications cell in our worksheet  look like after the selected values have been written to them. You may be wondering why the Word 2010 box is not checked? That’s because the list selections are cleared right after the values are written to the cells.

Well, I hope you find this useful — the people I work with sure did!

 

 Cells populated with the list choices

 

 – Gary Willoughby