Filtering column for input in ListBox

[Kristina96]

Hello everyone.

I have a document with a userform that allows users to select a specific area in a list box. Multiple selections are possible.
Based on the input I would like to filter column E called "AreaColumn" for the input made by a user when cofirming the user form with OK.

So far I've tried something like this:

Private Sub OKButton_Click()
ActiveSheet.Range("AreaColumn").AutoFilter Field:=1, Criteria1:=ListBox1.Value
End Sub

Unfortunately it does not bring the desired result but simply doesn't change the situation.
Could you help me with that?

Thank you in advance and best regards,
Kristina

 

[Fluff]

How about

Private Sub OKButton_Click()
   Dim Ary As Variant
   Dim i As Long, j As Long
   
   With Me.ListBox1
      ReDim Ary(1 To .ListCount)
      For i = 0 To .ListCount - 1
         If .Selected(i) Then
            j = j + 1
            Ary(j) = .List(i)
         End If
      Next i
   End With
   ActiveSheet.Range("AreaColumn").AutoFilter 1, Ary, xlFilterValues
End Sub

 

[Kristina96]

Thank you very much! It works perfeclty!
How do I have to edit the code to always include the option "everywhere" in the filter no matter what the input is?

 

[Fluff]

Is "everywhere" a value in the "AreaColumn"?

 

[Kristina96]

Is "everywhere" a value in the "AreaColumn"?

yes it is. in some rows in the areacolumn it says "everywhere" and those rows should always be shown.

 

[Fluff]

Ok, how about

Private Sub OKButton_Click()
   Dim Ary As Variant
   Dim i As Long, j As Long
   
   With Me.ListBox1
      ReDim Ary(1 To .ListCount + 1)
      For i = 0 To .ListCount - 1
         If .Selected(i) Then
            j = j + 1
            Ary(j) = .List(i)
         End If
      Next i
      Ary(j + 1) = "Everywhere"
   End With
   ActiveSheet.Range("AreaColumn").AutoFilter 1, Ary, xlFilterValues
End Sub

 

[Kristina96]

Works, perfectly! Thank you!
If the area selected. is eg. "Germany(Europe)", the filter is also supposed to include "Everywhere (Europe)". Is that possible? To include further criteria based on the input with an if-condition? I need the condition for various inputs.

Thank you very much for your support!

 

[Fluff]

What are all the possible criteria & need results based on the criteria?

 

[Kristina96]

the criteria is in the list box.
for example:
List Box1 includes Germany(Europe), France(Europe), China(Asia) and Brazil (Latin America)
those are also the different cells in the column AreaColumn
If I select Germany(Europe) in the listbox I want the macro to filter the AreaColumn for "Germany(Europe)", "All" and "All(Europe)".
Same goes for all other possible selections.

Does that make any sense?
Thank you in advance

 

[Kristina96]

Lastly, I don't want to filter at all in case the user doesn't select any option. That would probably be another if-condition?