Multi Select ListBox False Selection issue

[chirayu]

Hi Guys,

First off this macro is in a way related to the following thread

http://chandoo.org/forum/threads/vba-slicer-basic-code.27395/

Basically I decided to now create a pure UserForm version of the macro (God its so much simpler to code). But I'm having an issue with the Multi Select ListBox.

I'm using fmMultiSelectMulti

The way it checks if something is selected in the Listbox is as follows:

With ActiveSheet.PivotTables(SelPvt).PivotFields(SelPvtF)

    For i = 0 To Me.ListBox1.ListCount - 1
  
        If Me.ListBox1.Selected(i) = True Then
            On Error Resume Next
            .PivotItems(i + 1).Visible = True
        Else
            On Error Resume Next
            .PivotItems(i + 1).Visible = False
        End If
      
    Next

End With

Problem I'm having is that I sometimes get a False being filtered in the Pivot.

I think its because sometimes when I click on a different Item in the ListBox to select it & click on the previous item to deselect it, a faint dotted line remains around the previous item as if its highlighted. Wonder if that's causing the False to be seen as True.

Please can you guys help me fix this issue as I can't figure it out. This is the only thing I need to fix before I release the UserForm version into the Vault.

 

[Luke M]

When you step through code, have you verified that it's properly evaluating to False? You're other problem could be if it's trying to hide that last item in PivotTable before being able to show anything else. The PT has to be showing at least one item. Since you had the Resume Next statement in there, code wouldn't fail, and you'll miss it.

Might be better to have the PT unfilter everything to start, and then hide the things that aren't selected?

Side note, since you're dealing with Booleans, you could simplify this

        If Me.ListBox1.Selected(i) = True Then
            On Error Resume Next
            .PivotItems(i + 1).Visible = True
        Else
            On Error Resume Next
            .PivotItems(i + 1).Visible = False
        End If

To just this

.PivotItems(i + 1).Visible = Me.ListBox1.Selected(i)

 

[chirayu]

@Luke M

Thanks yeah I already checked that the True/ False is evaluating correctly. Ran it sentence by sentence.

I had a similar idea to yours but I went down the double filter route instead of unfilter & then filter. Amounts to pretty much the same thing. Done it your way, caught a bug my way

Also thanks for the tip. I didn't know I could just do .visible = .selected

Would there be a way to do .visible <> .selected? Tried doing <> & it gave me an error. Its for the Invert button lol

 

[Luke M]

Glad to hear you found the problem.

"Not" will reverse a Boolean.

.PivotItems(i + 1).Visible = Not Me.ListBox1.Selected(i)

 

[chirayu]

@Luke M tried that too but gives me an error - "Run-time error 1004: Visible method of PivotItem class failed"

In any case I already have a workaround. I was just asking to see if the code can be simplified further. I'll post the Userform version as a new thread shortly

Edit: My fault. Your code does works. I just did "Not" instead of "= Not"