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()

Unload Me End Sub

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

Post a comment