Trying to notate an applied filter, turn it off, then back on

[mcomp72]

In my workbook, I give the users the ability to turn on filtering for a few columns of a worksheet. I have other code that exports the worksheet to a new file. Sometimes, the export doesn't work correctly if the user has filtered enabled AND has an active filter applied. So I'm trying to do four things:

1. Notate the specifics of the applied filter.
2. Turn filtering off.
3. Run some other code.
4. Turn filtering back on and reapply the filter that was notated in step 1.

I ran across this thread when researching how to do this:
Capture Autofilter state

The very last answer seemed promising, so I am trying what that person suggested. I modified their code a little bit to have two subs: one that does #1 & #2 above, and the does #4 above. The code that does #4 isn't working, and I have no idea why. Here's the code.

For Steps # 1 & 2

Sub POLog_TurnOffFilteringIfNeeded(FilterApplied As Boolean, cv As CustomView)

With ThisWorkbook.Sheets("Purchase Order Log")

    If .AutoFilterMode = True And .FilterMode = True Then FilterApplied = True
    
    If FilterApplied = True Then

        Set cv = ThisWorkbook.CustomViews.Add(ViewName:="TempName", RowColSettings:=True)

        .AutoFilterMode = False

    End If

End With

End Sub

For Step #4

Sub POLog_TurnOnFilteringIfNeeded(FilterApplied As Boolean, cv As CustomView)

If FilterApplied = True Then
    
    If Not cv Is Nothing Then
        cv.Show
        cv.Delete
    End If

End If

End Sub

And here's the relevant code in the main sub that calls both of the above.

Dim FilterApplied As Boolean
Dim cv As CustomView

Call POLog_TurnOffFilteringIfNeeded(FilterApplied, cv)

[... other code here that exports the sheet ...]

Call POLog_TurnOnFilteringIfNeeded(FilterApplied, cv)

Set cv = Nothing

The sheet is exported properly, but filtering is not reapplied afterwards. Any help to understand what I need to do to make it work would be greatly appreciated.

 

[Micron]

From what I can see, your issue is that in the calling sub, the default boolean value is False because you don't actually set it, so for both calls, False would be passed.
You only need 2 subs: one that calls and one that deals with it. You should be passing the filter value to 1 other sub, not setting it's value in it.

'you don't need to Dim filtering here
Dim cv As CustomView
POLog_Filtering(False, cv) '<< you don't give cv a value either, so I suspect it's value is Empty or is Nothing

[... other code here that exports the sheet ...]
POLog_Filtering(True, cv)
Set cv = Nothing

I probably am not completely following what you're doing so this is just a guideline:

Sub POLog_Filtering(FilterApplied As Boolean, cv As CustomView)

With ThisWorkbook.Sheets("Purchase Order Log")
    If FilterApplied = True Then
        'do something for True
    Else
       'do something for false
    End If
End With

End Sub

The way your code is written makes me think you believe that each sub knows what the state of the variable is based on what code ran before. This is not the case. If that is the case, you might want to research variable scope.

 

[mcomp72]

From what I can see, your issue is that in the calling sub, the default boolean value is False because you don't actually set it, so for both calls, False would be passed.

When it calls the first sub (the TurnOff sub), both variables (the Boolean and the CustomView) are passed to it AND their values are changed in it. And then when it calls the second sub (the TurnOn sub), those values are passed to it. My understanding of how VBA works is that when I pass variables to a sub, it is passing a reference to those variables, meaning that sub can change them. That would mean those changes will be reflected in the main sub (the sub that calls the first & second sub). So when the variables are passed to the second sub, their values reflect any changes that were made to them in the first sub.

I ran the code, putting a break point in the second sub, and I added both of those variables to the Watch window. You can see in the screenshot below that FilterApplied (the Boolean) is set to True, and cv (the CustomView) also has a value.

So the problem is not that the variables are not properly defined when they are sent to the second sub. The problem seems to be with the line cv.Show. I had never heard of the CustomView variable type before I ran across this code today, so I am not familiar with it. I am not sure if the code the person posted in the other thread (the one I linked to in my initial post) actually works or not. Are you familiar with the CustomView variable type, and if so, can you confirm that cv.Show should be doing what I am wanting it to do? What I want is for it to enable Filtering and set it back to the way it was set before the first sub ran. That, of course, assumes that setting the cv variable in the first sub actually recorded the filtering information.

The CustomView article on the Microsoft website doesn't really tell me much of anything.
CustomView object (Excel)

This article has a bit more information, and it mentions that the RowColSettings is supposed to hold filter information, but still... there isn't a lot of detail in this one, either.
Excel class CustomView VBA

So I'm really not sure if the CustomView variable can even do what I want it to do.

 

[Micron]

Step through your code and watch. Unless it's different from what you've posted, the boolean variable value being passed in both calls is False because you don't give it a value. In both subs you have
If FilterApplied = True Then
but it never will be, so none of that which depends on the value of FilteredApplied will ever execute. If you intend to pas False both times, then your test for FilterApplied being True is incorrect.

 

[mcomp72]

Step through your code and watch. Unless it's different from what you've posted, the boolean variable value being passed in both calls is False because you don't give it a value. In both subs you have
If FilterApplied = True Then
but it never will be, so none of that which depends on the value of FilteredApplied will ever execute. If you intend to pas False both times, then your test for FilterApplied being True is incorrect.

I think you must have misunderstood my first reply. In that post I show that I DID step through the code, and I even showed a screenshot of it. The watch window is visible in the screenshot, and it shows that FilterApplied is equal to True in the TurnOn sub. This means that it must be being set to True in the TurnOff sub, because that is the only place in the code that would ever set it to True. The line of code in the TurnOff sub that would set it to True is:

If .AutoFilterMode = True And .FilterMode = True Then FilterApplied = True

I think I may have actually figured out the problem. As I have done more research on the issue, I ran across a website that said that if any sheet in the workbook is protected, showing a CustomView may not work, even if the only sheet actually affected is not protected. I haven't had time to test this yet, but I will this week.

 

[Micron]

Then somewhere in here

[... other code here that exports the sheet ...]

you must be altering the value because I copied your code and ran it and based on the code you posted, it was ever False. I overlooked the watch window results because of that. Hopefully you are on to something wrt protection because I've never dealt with customview in vba.

 

[mcomp72]

I can confirm that once I input code before the line "cv.Show" that unprotected all sheets in the workbook, the code worked as expected.