Autofilter Pull-down

[daveroblit]

Anyone know if there's any way to make an Autofilter pull-down display as a
scrolling list instead?

Thanks,

Dave

 

[CyberTaz]

Hi Dave -

The fact is they _are_ scrolling lists, it's just that they are designed to
display a lot of entries. My resolution is set at 1280 X 1024 which provides
approx 60 items in addition to the default selections at the top of the
list. If the number of entries exceeds that, the list becomes scrollable
automatically. The number of visible entries varies with resolution.

What you are actually asking for, I think, is a way to restrict the number
of viewable entries so the scrolling kicks in sooner, effectively making the
list shorter. There is no setting for that purpose, although there may be a
means o doing it programmatically. You can do just about anything if you
know how to write the code. That's a bit out of my territory, but keep
checking back as there are a number of folks who may have something to
offer.

Regards |:>)

 

[daveroblit]

Thanks. I've been reading up, and I think what I really want is a list box
that functions like an autofilter pull-down. It just displays 10 lines or so
at a time and has a scroll bar. Anyone know how to assign autofilter behavior
to a list box?

 

[JE McGimpsey]

Thanks. I've been reading up, and I think what I really want is a list box
that functions like an autofilter pull-down. It just displays 10 lines or so
at a time and has a scroll bar. Anyone know how to assign autofilter behavior
to a list box?

Here's how I've done it in the past. It's easier than it may appear...

First, define a dynamic name for your list so that it adjusts to
additions or deletions (note: this is optional, but the rest of the
discussion assumes you've done it). Assuming your list is in A2:Axxx in
Sheet1, with a list header in A1:

- Choose Insert/Name/Define
- Enter "mydynamiclist" (or something else, without quotes) in
the Names in workbook: box.
- Enter

=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A$2:$A$65536),1)

in the Refers to: box.

Next, create the listbox. Right/CTRL-click the box and choose Format
Control. In the Control pane, enter

=mydynamiclist

(or whatever name you chose) in the Input Range: box. Leave the Cell
link: box blank. Click OK. Right/CTRL-click the box again and choose
Assign Macro. In the Assign Macro dialog, the Macro name: should be
filled in with something like "Listbox1_Change". Click New. The Visual
Basic Editor will open with something like

Sub Listbox1_Change()
End Sub

Between the Sub.. and End Sub lines, paste the following (adjusting your
range and Listbox reference, and your Field offset value if necessary):


Dim vCriterion As Variant
With ActiveSheet
With .ListBoxes("List Box 1")
vCriterion = .List(.ListIndex)
End With
With .Range("mydynamiclist")(1)
.AutoFilter
.AutoFilter Field:=1, _
Criteria1:=vCriterion, _
Operator:=xlOr, _
Criteria2:="", _
VisibleDropDown:=False
End With
End With

I've put a demo workbook at

ftp://ftp.mcgimpsey.com/excel/daveroblit_demo.xls

Note that there's a bug in listbox/shape handling that expands the
listbox when the Autofilter is removed, even if the listbox properties
are set not to resize with cells. For that reason, I added some extra
code to the demo to position the listbox at cell F1. I collected all the
constant data at the top to make it easier to change:

Public Sub ListBox1_Change()
Const sDynamicRangeName As String = "mydynamiclist"
Const sListBoxName As String = "List Box 1"
Const sListBoxCell As String = "F1"
Const nListBoxHeight As Long = 120
Const nFieldOffset As Long = 1

Dim vCriterion As Variant
Dim rListBoxCell As Range

Application.ScreenUpdating = False
With ActiveSheet
Set rListBoxCell = .Range(sListBoxCell)
With .ListBoxes(sListBoxName)
vCriterion = .List(.ListIndex)
End With
With .Range(sDynamicRangeName)(1)
.AutoFilter
.AutoFilter Field:=nFieldOffset, _
Criteria1:=vCriterion, _
Operator:=xlOr, _
Criteria2:="", _
VisibleDropDown:=False
End With
With .ListBoxes(sListBoxName)
.Top = rListBoxCell.Top
.Left = rListBoxCell.Left
.Height = nListBoxHeight
End With
End With
Application.ScreenUpdating = True
End Sub

Unfortunately, this slows things down a bit, but it's better than having
the listbox resizing. It's not necessary if the listbox is not in the
vertical range of the autofilter.

 

[daveroblit]

Wow, JE, thanks! That's awesome.

Do you do this kind of work for a living? I'm looking to have a custom
spreadsheet done using this and a few other features, plus a bunch of macros
to edit text strings.

This is pretty much over my head, but if I wanted the first line of the
listbox to be "ALL", could that be done?

Dave

 

[JE McGimpsey]

Do you do this kind of work for a living?

in addition to some other things:

http://www.mcgimpsey.com

I'm looking to have a custom spreadsheet done using this and a few
other features, plus a bunch of macros to edit text strings.

You could contact me, or post further questions here for free.

This is pretty much over my head, but if I wanted the first line of the
listbox to be "ALL", could that be done?

Sure. The easiest way would be to put "ALL" in your data list as the
first element (A2 in my demo). Then, if you want ALL to show all
entries, modify the code I gave you:

With .Range(sDynamicRangeName)(1)
.AutoFilter
If Not UCase(vCriterion) = "ALL" Then _
.AutoFilter Field:=nFieldOffset, _
Criteria1:=vCriterion, _
Operator:=xlOr, _
Criteria2:="", _
VisibleDropDown:=False
End With

 

[daveroblit]

Hi JE,

I just sent you an e-mail to the contact link on your Web site with the
project details. Please let me know what you think.

Thanks,

Dave