Parameter name: request
Translate Request has too much data
Parameter name: request
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.
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.
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."
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.
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.
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:
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.
Then the second subroutine, named ClearBoxSelections, runs. It clears the selections from the list box, and selects the original active cell.
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)
'Clear all of the box selections from the list box
ClearBoxSelections
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!
-- Gary Willoughby
No comments:
Post a Comment