Autofilter with combo box in Excel

[travisbrown]

I have a workbook with a data table on one sheet, and a chart on another. I can filter the data using autofilter on the datasheet, but want to use a combo box on the chart sheet to control the chart display.

Any directions on how to do this?

 

[SkipVought]

Hi,

What verion Excel?

What kind of control (Forms, Control Toolbox, Data Validation)?

Is the chart embedded in a sheet or is it a Chart Sheet?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[travisbrown]

Hmm...

Started in Excel 2008 Mac, now in Excel 2003.

I just drew a combo box from the forms palette

Chart is just embedded on a sheet. Actually just cut and pasted from the datasheet after creating the chart and selecting the named ranges.

 

[SkipVought]

Excel 2003, Forms control, embedded chart

So the Linked Cell returns the result of your pick. Its an INDEX to the VALUES in your list. The Selected Value can be calculated...
[tt]
=INDEX(YourListRangeRef,LinkedCellRef,1)
[/tt]
Turn you your macro recorder and record selecting a value in the AUTO FILTER.

Post back your recorded code.

Also completely explain where the list of value is that you reference in your combobox. (sheet, cell reference, named range reference etc)


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[travisbrown]

So I changed it to a Control Toolbox.

It's pulling data from a named range in another worksheet (manually managed list because I can't figure out how to get a distinct list in the combo but using the column it's supposed to control)

It needs to control the autofilter on Data!$D$1

I was trying this:

Private Sub ComboBox1_Change()
    Range("ComboSites").AutoFilter Field:=1, Criteria1:=ComboBox1.Value, visibledropdown:=False
End Sub

 

[travisbrown]

So I jacked some code off the internet and got working. Does this look reasonable?

Option Explicit
Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
If Sheets("Data").AutoFilterMode = True Then Worksheets("Data").Range("D1", Worksheets("Data").Range("d60000").End(xlUp)).AutoFilter
Worksheets("Data").Range("D1", Worksheets("Data").Range("d60000").End(xlUp)).AutoFilter Field:=1, Criteria1:=ComboBox1.Value, visibledropdown:=False
Application.ScreenUpdating = True
End Sub

I didn't realize that MS had lifted VBA right out of Excel Mac. I guess there is no way to get this working on it.

 

[SkipVought]

Private Sub ComboBox1_Change()
    Application.ScreenUpdating = False
    
    With Sheets("Data")
        If Not .AutoFilterMode Then _
            .Range("D1").AutoFilter
            
        .Range("D1").AutoFilter _
            Field:=1, _
            Criteria1:=ComboBox1.Value, _
            visibledropdown:=False
    End With
    
    Application.ScreenUpdating = True
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}