Database Reference
In-Depth Information
box choice drives what selections appear in the next dropdown. For instance, if the
user chooses “Audio Systems” from the Segment dropdown, the Channel dropdown
would be populated based on a lookup to the table where our valid sparse interdi-
mensional relationships are stored (Figure 10.6): “Distribution,” “government,”
“Education.” If the user then selects “government” from the Channel dropdown, the
Entities dropdown would be limited to “north America” and “Latin America” based
on the Segment -> Channel -> Entities interdimensional relationship established for
this worksheet.
here's how to look up a list of members in one dimension that correspond to the
member selected in another dimension:
Sub lookupDimMemberList(memberNm As String, _
memberDim As String, _
lookupDim As String, _
lookupDimMemberList As Collection)
Dim cell As Range
Dim lookupMatch As String
For Each cell In Range(memberDim)
' Does the current row contain our member?
If Range(memberDim).Cells(cell.row).Value = memberNm Then
' If so, then our lookup is in the same row in the
' lookupDim range
lookupMatch = Range(lookupDim).Cells(cell.row).Value
'we only want unique members
On Error Resume Next
lookupDimMemberList.Add lookupMatch, lookupMatch
On Error GoTo 0
End If
Next cell
End Sub
Simply put, the lookup table of valid member combinations provides a list of what
members may be loaded in a dropdown based on the selection in another dropdown
using the preceding method.
10.4.9 Adding the Combo Boxes
Let's put this together with some custom Pov dropdown selections. on the Developer
ribbon, click Insert, Activex Controls, Combo Box. use the cross cursor to “draw” the
Combo Box near the top of your spreadsheet. In the range names dropdown, change the
name to “cboSegment” (Figure 10.7).
now do the same thing for the “cboChannel” and “cboEntity” Combo Boxes
(Figure 10.8).
Figure 10.7
cboSegment Combo Box.
