Database Reference
In-Depth Information
Figure 10.8
cboSegment, cboChannel, cboEntity.
10.4.10 Adding the Initialization Code
In the vB code, double click “ThisWorkbook,” then click the dropdown on the left and
select “Workbook.” This will insert the Workbook_open() method.
Add the call to InitCombo to the method. Then, below the Workbook_open method,
add the following code to initialize the first Combo Box.
Private Sub InitCombo()
Dim segmentRange As Range
Dim cell As Range
Dim col As New Collection
Dim item As Variant
Sheet3.cboSegment.Clear
Set segmentRange = Range("Segment")
For Each cell In segmentRange.Cells
On Error Resume Next
col.Add cell.Value, cell.Value
On Error GoTo 0
Next cell
For Each item In col
Sheet3.cboSegment.AddItem item
Next item
End Sub
This will initialize the Segment Combo Box with a unique list of Segment members
based on the segment range in our lookup.
10.4.11 Adding Events to Drive the Combo Boxes
The following will demonstrate how to add events to the cboSegment and cboChannel
Combo Boxes. This is the “magic” behind managing the user so that he only chooses
valid combinations of members for data entry. When a combo box selection is changed,
the Change event for that control fires. We will implement this event to do the lookup
and load the next Combo Box with only members that are valid with the current selec-
tion. In other words, if a user chooses the vCrs segment, only the government member
will be available as a choice in the cboChannel Combo Box.
to add an event to the Combo Box, double click on the ThisWorkbook object
listed under “microsoft Excel objects” in the vBA editor, This opens the module for
ThisWorkbook, which is where we will add our events (Figure 10.9).
