Combo Box - "ALL" selection 1

[MSIsam]

I created a form combo box that filters data in a spreadsheet (Excel 2003) and would like the first value to be "ALL". When selected, all the values would be shown. This is the code that I currently have:

Sub DropDown20_Change()
  Dim r As Range
  Set r = Range("DATASET")
  
  r.AdvancedFilter xlFilterInPlace, Range("ref")
  Range("A1").Select

End Sub
Any ideas?

Thanks,
Sam

 

[xlbo]

what other values do you have in the combo and how does it interact with the code as from what you have there, the same thing will happen whatever is selected in the combobox.
Can you give any more info about your setup ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MSIsam]

Apologize for my very limited VB knowlege.

The Input Range is a Named range

Division: =Reference!$A$3:$A$20

which is a list of values on a different worksheet. These values are 3 digit numbers.

The Cell Link is next to that range.

Reference!$B$2:$B$2

The Named Range "Ref" is a formula that returns the division based on the combo box selection.

=INDEX(Division,B2,1)

I can add "ALL" to the Division range so that it shows up in the drop down but cannot figure out how to make that essentially remove all filtering when selected.

Is this the information that you need?

 

[SkipVought]

1. Use AutoFilter instead of Advanced Filter

2. Turn on your macro recorder and select (All) in one of the filters

3. observe your recorded code.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[DaleWatson123321]

MSIsam,

Here are step-by-step instructions that will provide you with what you’re seeking.

I suggest you set up a separate file with these instructions, and then apply to your file what you’ve learned from this example.

1) On Sheet1, modify the tab name to “Database”.
2) Enter “colmA” in cell A1, enter “colmB” in B1.
3) Enter “record_1” in A2, enter “record_2” in A3.
4) Enter the value “1” in B2, and “2” in B3.
5) Select A2:B3. Click-and-Drag this selected range down to row 150.
6) Select A1:B150, and assign the range name “dataset”.

Here’s my method of assigning a range name.
a) After highlighting the range,
b) Hold down <Ctrl> and hit <F3>,
c) Type the name – in this case: dataset
d) Hit <Enter>

7) On Sheet2, modify the tab name to “Criteria”.
8) In A2, enter the label: “pick” (don’t enter any quotation marks).

Note: Re #8, I have a practice of placing text (a label) next to the range where I will be assigning a range name. This is very useful for future reference. Otherwise, it would be more difficult to determine where the range names are assigned.

9) In B2, assign the range name: pick
10) In A3, enter “crit_type”. In B3, assign the range name: crit_type.
12) In A5, enter “tbl”. In B5, enter “list”
13) In A6:A11, enter the numbers 1 to 6.
14) In B6:B10, enter: crit1, crit2, crit3, crit4, crit5. In B11, enter: ALL
15) Select A6:B11, and assign the range name: tbl
16) Select B6:B11, and assign the range name: list

17) In B3, enter this formula: =VLOOKUP(pick,tbl,2) (The formula will be “#N/A” until a value is entered into B2. (“pick” will be the name you later enter as your Cell link in the Combo Box).

18a) In A13, enter: crit1.
18b) Highlight B13:B14, and assign the range name: crit1
18c) Enter this formula in B14: =AND(colmB>$D$14,colmB<=$E$14)
18d) In D14, enter: 0, In E14, enter: 10

19a) In A16, enter: crit2.
19b) Highlight B16:B17, and assign the range name: crit2
19c) Enter this formula in B17: =AND(colmB>$D$17,colmB<=$E$17)
19d) In D17, enter: 10, In E17, enter: 20

20a) In A19, enter: crit3.
20b) Highlight B19:B20, and assign the range name: crit3
20c) Enter this formula in B20: =AND(colmB>$D$20,colmB<=$E$20)
20d) In D20, enter: 20, In E20, enter: 30

21a) In A22, enter: crit4.
21b) Highlight B22:B23, and assign the range name: crit4
21c) Enter this formula in B23: =AND(colmB>$D$23,colmB<=$E$23)
21d) In D23, enter: 30, In E23, enter: 40

22a) In A25, enter: crit5.
22b) Highlight B25:B26, and assign the range name: crit5
22c) Enter this formula in B26: =AND(colmB>$D$26,colmB<=$E$26)
22d) In D26, enter: 40, In E26, enter: 50

23a) In A28, enter: ALL
23b) Highlight B28:B29, and assign the range name: ALL
23c) In B28, enter: colmB
23d) Make sure B29 is left BLANK. This will permit ALL records to be accepted.
(You could enter a label in D29 – e.g. “Blank – to show all records”)

24) Now go back to the Database sheet, and create a Combo Box from the Forms toolbar.
25) Right-click on the ComboBox, and choose “Format Control”.
26) For Input range, enter: list
27) For Cell link, enter: pick

28) Copy the following VBA code into a Module. (For those new to VBA… <Alt> <F11> to enter the VBA Editor, then from the menu, use: Insert Module.)

Sub DropDown_Change()
crit = [crit_type].Value
Range("dataset").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=crit, _
Unique:=False
End Sub

29) <Alt> q …can be used to exit the VBA Editor.

30) Back to the ComboBox. Right-click, and choose “Assign Macro”, and then select the above DropDown_Change

31) One last change to the ComboBox Properties… Right-click, choose Format Control and Properties – and change to “Don’t move or size with cells” - otherwise your ComboBox will move when records are filtered. (and you’ll likely also want to de-select “Print”.

You’re now ready to test out the ComboBox. I hope everyone is able to follow this example, and I hope it helps.

Regards, Dale Watson

 

[MSIsam]

Thanks everyone for your help.

Dale, this is perfect. It was very easy to follow and really helped me understand how the combo box works.

Thanks Again!
Sam

 

[DaleWatson123321]

MSIsam,

You're quite welcome. I'm pleased you found the instructions "very easy to follow", and helped. Thanks for the feedback.

Here's a little more helpful information that can further expand your knowledge in this area...

In addition to "FilterInPlace", an option is to use "FilterCopy". The advantange of this can be an easy way to extract selective data to a SEPARATE sheet to produce custom reports.

On the separate sheet, you'll have freedom to do things that you would likely not want to do on your dataset sheet. For example, you could:
a) allow rows at the top for special headings.
b) modify the formatting,
c) have pre-set page settings, margins, etc.
d) you can even re-arrange the data by changing the order of the field names.

The following is a slightly modified version of the previous code. It also extracts the data selected by the pulldown choice.

Dim crit As String

Sub DropDown_Change()
    crit = [crit_type].Value
    Range("dataset").AdvancedFilter Action:=xlFilterInPlace, _
    CriteriaRange:=crit, _
    Unique:=False
    Extract_Data
End Sub

Sub Extract_Data()
    Range("dataset").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=crit, _
    CopyToRange:=Range("ext"), _
    Unique:=False
End Sub

I hope this also proves useful.

Regards, Dale Watson