## Case Study Using Formula Based Conditions in the Excel User Interface

You can use formula-based conditions to easily solve the report introduced in the prior case study.

To illustrate,o^ to the right of the criteria range,set up a column of cells with the list of selected customers. Assign a name to the range such as MyCust. In cell J2 of the criteria range enter a formula such as =Not(ISNA(Match(D2,MyCust,False))).

To the right of the MyCust range,set up a range with a list of selected products. Assign this range the name of MyProd. In the K2 of the criteria range, add a formula to check products, =NOT(iSNA(Match(B2,MyProd,Faise))).

To the right of the MyProd range,set up a range with a list of selected regions. Assign this range the name of MyRegion. In L2 of the criteria range,add a formula to check for selected Regions, =NOT(ISNA(Match(A2,MyRegion,False))).

Now,with a criteria range of J1:L2,you can effectively retrieve the records matching any combination of selections from the user form.

### Using Formula Based Conditions with VBA

The following is the code for this new userform. Note the logic in OKButton_Click that builds the formula. Figure 11.15 shows the Excel sheet just before the Advanced Filter is run.

Private Sub CancelButton_Click()

Private Sub cbSubAll_Click()

For i = 0 To lbCust.ListCount - 1 Me.lbCust.Selected(i) = True Next i End Sub

Private Sub cbSubClear_Click()

For i = 0 To lbCust.ListCount - 1 Me.lbCust.Selected(i) = False Next i End Sub

Private Sub CommandButton1_Click() ' Clear all products For i = 0 To lbProduct.ListCount - 1 Me.lbProduct.Selected(i) = False Next i End Sub

Private Sub CommandButton2_Click() ' Mark all products For i = 0 To lbProduct.ListCount - 1 Me.lbProduct.Selected(i) = True

0 0