mercredi 24 juin 2015

VBA: Count the amount of different items are seleted in a filter

In Excel I have a filter on a header, this filter allows the user to select different areas and show the records that match the areas selected. I have the following code that then displays the value that has been selected in the filter:

Dim ftrRegion As Filter
On Error GoTo stopError
Set ftrArea = ActiveSheet.AutoFilter.Filters(2)
Set ftrRegion = ActiveSheet.AutoFilter.Filters(1)

If ftrRegion.On Then
    Application.EnableEvents = False
    Debug.Print ftrRegion.Criteria1
    Range("E206").Value = Mid$(ftrRegion.Criteria1, 2)
    Range("F201").Value = "Region" & " " & Mid$(ftrRegion.Criteria1, 2)
Else
    Range("E206").Value = ""
End If

But the problem with this is that if a user selects more than one value the whole thing doesn't work, so what I want is a way to count the amount of values the user has selected in the filter, and if it is just the 1 then apply this code through an if statement. Can someone please help.

Aucun commentaire:

Enregistrer un commentaire