Fairly often in VBA code I need to offer the user a list and have them make a choice, like picking which open workbook to do something to. I created a function and a userform to handle these situations. (Around the house, I call the form “ChooserForm” but it’s given name is “frmChooser.”) The function takes an array of choices and a caption as its arguments. The function loads frmChooser and passes it the string array and the caption. When the user makes a choice and clicks OK the function returns the choice to the calling routine.
Let’s look at how it works, starting from the inside out (by which I mean with the userform):
The frmChooser UserForm
Private mChoiceList() As String
Public Property Let ChoiceList(PassedList() As String)
mChoiceList() = PassedList()
End Property
Private Sub UserForm_Activate()
With Me.cboChooser
.List = mChoiceList()
.ListIndex = 0
End With
End Sub
Public Property Get ChoiceValue() As String
ChoiceValue = Me.cboChooser.Value
End Property
Private Sub cmdOk_Click()
mboolClosedWithOk = True
Me.Hide
End Sub
Public Property Get ClosedWithOk() As Boolean
ClosedWithOk = mboolClosedWithOk
End Property
Private Sub cmdCancel_Click()
mboolClosedWithOk = False
Me.Hide
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'in case the user clicked the "X"
If CloseMode = vbFormControlMenu Then
Cancel = True
cmdCancel_Click
End If
End Sub
The form has three custom properties. The first, Let ChoiceList, assigns the array of choices to the form’s module-level variable, mChoiceList(). On form activation the combobox cboChooser’s list is filled with the mChoiceList array.
The second property, Get ChoiceValue, is the currently selected value of the combobox. The function will “get” this, after the OK button is clicked, to determine the user’s choice.
The third property, Get ClosedWithOk tells the calling function whether the user hit the OK button. If it’s True then the function will do its processing. If it’s false, then the user hit the Cancel button or the “X,” and we’ll skip the processing.
The Function code
Dim ufChooser As frmChooser
Dim strChoicesToPass() As String
'why is this necessary?
ReDim strChoicesToPass(LBound(strChoices) To UBound(strChoices))
strChoicesToPass() = strChoices()
Set ufChooser = New frmChooser
With ufChooser
.Caption = strCaption
.ChoiceList = strChoicesToPass
.Show
If .ClosedWithOk Then
GetChoiceFromChooserForm = .ChoiceValue
End If
Unload ufChooser
End With
End Function
The function creates an instance of frmChooser, called “ufChooser,” passes the Caption and ChoiceList properties and shows the form. After the .Show command, processing passes into the form and the code shown in the previous section. Processing returns to the function when the form is hidden, by either the OK or Cancel button’s click event. The function then checks the form’s ClosedWithOK property. If it’s true the function returns the form’s ChoiceValue property – the value selected in the combobox – to the calling routine.
You may have noticed the question “why is this necessary?” I can’t just pass strChoices() straight into the frmChooser instance. It causes a runtime “internal error.” Instead I have to declare a second string array strChoicesToPass() and copy the first array to it. If anybody can explain why, please share! (I think I could pass a variant straight through, but I don’t.)
The general form of this function’s code, and that of the userform, is from the venerable Professional Excel Development.
Using the Function
Now that we’ve got the function and the form, let’s choose something! I’ve got some code below that lists all the visible fields in a pivot table. When one is picked, the data range for the field is highlighted, along with the source column in the table, and the fields source name is displayed:
Dim pvt As Excel.PivotTable
Dim lo As Excel.ListObject
Dim StartingCell As Excel.Range
Dim i As Long
Dim PivotFieldNames() As String
Dim pvtField As Excel.PivotField
Dim ChosenName As String
Set pvt = ActiveSheet.PivotTables("pvtRecordTemps")
Set lo = ActiveSheet.ListObjects("tblRecordTemps")
Set StartingCell = ActiveCell
With pvt
ReDim PivotFieldNames(1 To .VisibleFields.Count) As String
For i = 1 To .VisibleFields.Count
PivotFieldNames(i) = .VisibleFields(i).Name
Next i
ChosenName = GetChoiceFromChooserForm(PivotFieldNames, "Choose a Pivot Field")
If ChosenName = vbNullString Then
Exit Sub
End If
Set pvtField = .PivotFields(ChosenName)
With pvtField
Union(.DataRange, lo.ListColumns(.SourceName).DataBodyRange).Select
MsgBox Title:=.SourceName, _
Prompt:="The SourceName for " & ChosenName & " is:" & vbCrLf & vbCrLf & .SourceName
End With
StartingCell.Select
End With
End Sub
This type of code can be useful when the PivotField names have been changed drastically from their underlying SourceNames, especially if the SourceNames are cryptic, similar, and there’s lots of them. In the picture below the SourceNames in the table were “Field 1”, “Field 2”, etc., but were changed to meaningful names like “Continent” in the pivot table.
Here’s the sample workbook for your downloading pleasure.
Pingback: Hide Pivot Table Single-Item Subtotals - yoursumbuddy
Pingback: Flexible Chooser Form With Apply Button - yoursumbuddy
I enjoy this use of your code and implementation of it. Question: Would it be possible to change from a combo box to a Listbox?
Hi Troy. I would say it’s absolutely possible. I would just replace the combobox with a listbox and do a search and replace of the combobox name with the listbox name in the userform. The two properties used by the combobox in the userform code above also exist in listboxes.
Feel free to post back here how it goes.