Thread: Return to same autofilter setting

I have a workbook which, among other things, has two pages in relationship with each other. On the PAC sheet, I have a chart which displays percentages from the data on the DivReg_PAC sheet. In PAC, the user uses a Data Validation to choose which region they want to look at. Their choice triggers an autofilter on DivReg_PAC.

The PAC viewers can choose in another validation list to go see the DivReg_PAC sheet if they want to see the actual dollar numbers.

My problem occurs when they want to return to PAC via my command button in DivReg_PAC. My current code in the PAC sheet is that whenever the sheet is activated range E1 (my data validation cell) contains a vbnullstring. This is because when the user comes in I don't know what PAC he'll want to see, so use the vbnullstring. But, when the user is coming from the DivReg_PAC sheet, I'd like him to return to the same view he left. Below I have my present code for both sheets. My hope is that someone can help me reconfigure it so the user can return to the same view he left.

Here's the Worksheet Activate code for the PAC sheet:

Private Sub Worksheet_Activate()
    ActiveWindow.DisplayHeadings = False
    ActiveSheet.Protect
    Range("E1").FormulaR1C1 = vbNullString
    Range("N1").FormulaR1C1 = vbNullString
    
Application.EnableEvents = False
Application.ScreenUpdating = False

    With Sheets("DivReg_PAC")
        
        .Activate
        .Unprotect
        .AutoFilterMode = False
        .Columns("A:A").Select
        .Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("AB1"), Unique:=True
                
    End With

Application.ScreenUpdating = True
Application.EnableEvents = True

    ActiveWorkbook.Names.Add Name:="Divs3", RefersToR1C1:= _
        "=OFFSET(DivReg_PAC!R1C28,1,0,COUNTA(DivReg_PAC!C28),1)"
End Sub

When the user chooses to go to DivReg_PAC, their location is put into cell AA1. When they're ready to leave, they click the command button. Here's the command button code:

Private Sub CommandButton1_Click()    'Return to Chart

    Application.ScreenUpdating = False
    Worksheets(Range("AA1").Text).Activate
    Worksheets("LBB Opr Actvty").Visible = False
    Worksheets("PAC and LBB Acct").Visible = False
    Worksheets("PAC").Visible = True
    Sheets("DivReg_PAC").Visible = False
    Application.ScreenUpdating = True

End Sub

Thanks in advance for any help.

hi, jomili, if I understood everything right:

1. Do not change E1, N1 on sheet_activate event
2. Put vbNullString for PAC sheet E1, N1 cells through workbook_open event or workbook before close event

Congratulations Watersev, you WERE able to understand my poor explanation.

Making the changes you suggested did the trick, but when I went back to PAC the view of my chart wasn't filtered by what was in E1 until I reselected it, so there still work to do. I don't see anything in my code that would unfilter DivReg_PAC upon leaving, but that's what's happening; the chart displays ALL of the information, not just the autofiltered info, though E1 shows what I last selected.

Update: I forgot, there IS something that changes the view. When I go into the PAC view, I may be coming from any of 15 different worksheets. So, whenver PAC is acitivated, the DivReg_Pac autofilter is removed, so that the values in colmumn A can be used (after an advanced filter is applied) to create the Divs3 named range, which fuels the options in the PAC data validation list.

So, I'd need something in the PAC worksheet_Activate that would recognize that we're coming from the DivReg_Pac sheet, and NOT to remove the autofilter or redo the Div3 named range.

, well it was a quess, can you post sample workbook (data is not important, just the structure with sheet objects) with a couple of sheets?

I'll try. It's pretty big, so I'll have to really pare it down. Might not have it until tomorrow.

strip down everything to reproduce exact bottleneck for the issue, that's it

Okay, I stripped it as much as I could, while still trying to retain most of the functionality. You navigate through by choosing the options in the blue region to the right of the chart. You select the options you want to see in your view by selecting from the options on the left of the chart.

Ideally, I want to be able to open the tool and have it set to nothing (no data, blank chart) or all of the information (no selection). From there the user should select whatever they want to see, and the view fills. When they go into Details, and then return, or when they go to another view, or return, I'd like the same selections to autofill, so wherever they go in the workbook they'll have the same material on view, just a different level of detail, but they'll also have the option to change their selections on the fly.

Sorry it's so complicated and clunky. Any help on making it better is greatly appreciated.

Thanks,
John

Still wanting help on this one.

Well, problem solved. I found some neat code out here: http://www.ozgrid.com/forum/showthread.php?t=76618
which helped me solve the problem. My functioning code is shown below:

Sub Filter_All_Sheets()
'This one applies the autofilter from the MFR Adjustments page to all of the detail pages
     
    Dim objSheet As Worksheet, objMAinSheet As Worksheet
    Dim arrAllFilters() As String
    Dim byteCountFilter As Byte, i As Byte
     
    Set objMAinSheet = ActiveSheet
     'insert all criteria and address
    If insertAllFilters(arrAllFilters, byteCountFilter) Then
         
        Application.ScreenUpdating = False
         'If is allright, go on
        
        For Each objSheet In Sheets(Array("Projections", "Projections2")) 'use this code to do specific sheets
        'For Each objSheet In ActiveWorkbook.Worksheets 'use this code for every sheet

             'Don't refilter the activesheet
            If objSheet.Name <> objMAinSheet.Name Then
                 
                On Error GoTo errhandler
                 'check Autofilter, if one is off = switch on
                objSheet.Select
                objSheet.AutoFilterMode = False
                If Not objSheet.AutoFilterMode Then
                     ' if sheet doesn't contain some data
                    Range("A11:C11").AutoFilter
                End If
                 
                 'here I know that Autofilter is On
                 'filter some item
                For i = 1 To byteCountFilter
                     'only 1 criteria (without Operator)
                    If arrAllFilters(2, i) = 0 Then
                        Range("A11:C11").AutoFilter _
                        Field:=Range(arrAllFilters(4, i)).Column, _
                        Criteria1:=arrAllFilters(1, i)
                         'with operator
                    ElseIf arrAllFilters(2, i) <> 0 Then
                        Range("A11:C11").AutoFilter _
                        Field:=Range(arrAllFilters(4, i)).Column, _
                        Criteria1:=arrAllFilters(1, i), _
                        Operator:=arrAllFilters(2, i), _
                        Criteria2:=arrAllFilters(3, i)
                    End If
                Next i
                 
            End If
        Next objSheet
    Else
         'When Main Sheet doesn't contain data or Autofilter is off
        MsgBox "Main Sheet (Name """ & objMAinSheet.Name & """) is missing some data or the autofilter is not being used!" _
        & vbCrLf & "Try filtering on any column first." & vbCrLf & "This code can't go on.", vbCritical, "Missing Autofilter object or filter item "
         
        Set objMAinSheet = Nothing
        Set objSheet = Nothing
         
        Application.ScreenUpdating = True
         
        Exit Sub
    End If
     
    objMAinSheet.Activate
    Set objMAinSheet = Nothing
    Set objSheet = Nothing
     
    Application.ScreenUpdating = True
     
    MsgBox "Finished"
    Exit Sub
     
errhandler:
    Set objMAinSheet = Nothing
    Set objSheet = Nothing
     
    Application.ScreenUpdating = True
     
    If Err.Number = 1004 Then
        MsgBox "Probable cause of error - sheet doesn't contain some data", vbCritical, "Error Exception on sheet " & ActiveSheet.Name
    Else
        MsgBox "Sorry, run exception"
    End If
     
End Sub



Function insertAllFilters(arrAllFilters() As String, byteCountFilter As Byte) As Boolean
     ' go through all filters inserting their address and criterial
    Dim myFilter As Filter
    Dim myFilterRange As Range
    Dim boolFilterOn As Boolean
    Dim i As Byte, byteColumn As Byte
     
    boolFilterOn = False: i = 0: byteColumn = 0
     ' If AutoFilter is off - return False
    If Not ActiveSheet.AutoFilterMode Then
        insertAllFilters = False
        Exit Function
    End If
     
     ' If Autofilter is on & no filter any item = return false
    For Each myFilter In ActiveSheet.AutoFilter.Filters
        If myFilter.On Then
            boolFilterOn = True
            Exit For
        End If
    Next myFilter
     ' Check Filter
    If Not boolFilterOn Then
        insertAllFilters = False
        Exit Function
    End If
     
    On Error GoTo errhandler
     ' here is where all the control is done
    With ActiveSheet.AutoFilter
        For Each myFilter In .Filters
            byteColumn = byteColumn + 1
            If myFilter.On Then
                i = i + 1
                ReDim Preserve arrAllFilters(1 To 4, 1 To i)
                arrAllFilters(1, i) = myFilter.Criteria1
                arrAllFilters(2, i) = myFilter.Operator
                If myFilter.Operator <> 0 Then
                    arrAllFilters(3, i) = myFilter.Criteria2
                End If
                arrAllFilters(4, i) = .Range.Columns(byteColumn).Cells(1).Address
            End If
        Next myFilter
    End With
     
    byteCountFilter = i
    insertAllFilters = True
    Set myFilter = Nothing
    Set myFilterRange = Nothing
    Exit Function
     
errhandler:
    insertAllFilters = False
    Set myFilter = Nothing
    Set myFilterRange = Nothing
     
End Function