필터링 정보 표시하기

Step 1: 어떤 질문이었을까요?

아래와 같이 자동 필터가 설정된 데이터가 있습니다. 임의의 필드를 대상으로 필터링을 해 보세요.

데이터는 월, 팀장, 영업사원, 브랜드, 판매수량, 판매금액 여섯 개 필드로 구성되어 있고 머리글에 자동 필터가 설정되어 있습니다.

필드 옆의 드롭다운 버튼을 눌러 필터를 설정하면,

아이엑셀러

이와 같이 메시지 박스가 나타나면서 어떤 필드에 어떤 조건으로 필터링이 되었는지에 대한 정보를 알려주고 있습니다. 희안하지요? ^^

Step 2: Calculate 이벤트를 이용한 트릭

어떻게 하면 (자동 필터링 된 상태에서) 드롭다운 버튼을 클릭하면 리얼 타임으로 특정한 동작을 하도록 할 수 있을까요? VBA 공부를 아주 열심히 하신 어느 분이 그러시는군요.

클릭 이벤트로는 해결할 수 없습니다. 해서 약간의 트릭을 사용 하였습니다. 바로… 워크시트 오브젝트의 Calculate 이벤트를 사용한 것입니다.

그런데 이 Calculate 이벤트는 워크시트가 재계산 될 때마다 발생을 하므로 휘발성 함수의 하나인 Today 함수를 사용하여 어떤 변동 사항이 있을 때마다 재계산이 되도록 설정하였습니다. 단순히 보기 좋으라고 요렇게 표시한 것이 아니랍니다.

Step 3: 코드 살펴보기

코드를 살펴보도록 하지요.

먼저 Worksheet 개체의 Calculate 이벤트 프로시저이고, 이어지는 FilterInfo 함수는 일반 모듈 시트에 작성되어 있습니다.

Private Sub Worksheet_Calculate()

    Dim objFilter As Object
    Dim rngTitle As Range
    Dim lngRow As Long
    Dim intCol As Integer
    Dim strMsg As String
    Dim strInfo As String
    On Error Resume Next
    Set rngTitle = Range("Title")
    ' 데이터의 헤더 부분에 Title 이라는 이름을 미리 정의해 두고 불러다 사용합니다.

    With ActiveSheet.AutoFilter.Range
    ' 자동 필터가 설정되어 있는 영역의 위치 정보를 lngRow와 intCol 변수에 담아 둡니다.

        lngRow = .Rows(1).Row
        intCol = .Columns(1).Column
    End With

    For Each objFilter In ActiveSheet.AutoFilter.Filters
    ' 필터링 되어 있는 필드를 찾아 냅니다.

        If objFilter.On Then
        ' 필터링 된 필드이면 셀 색상을 빨간 색으로, 글자색을 흰 색으로 표시하고…

            With Cells(lngRow, intCol)
                .Interior.ColorIndex = 3
                .Font.ColorIndex = 2
            End With
        Else
            With Cells(lngRow, intCol)
                .Interior.ColorIndex = xlColorIndexNone
                .Font.ColorIndex = 1
            End With
        End If
        intCol = intCol + 1
    Next objFilter

    strInfo = FilterInfo(rngTitle)
    ' 필터링 정보를 파악하기 위한 사용자 지정 함수인 FilterInfo를 호출합니다.
    ' 이 때 rngTitle, 즉 표의 header 부분을 함께 인수로 건네 줍니다.

    If strInfo = "" Then Exit Sub

    strMsg = strMsg & "<필터링 Information>" & vbCr & vbCr
    strMsg = strMsg & strInfo
    MsgBox strMsg, , "www.iExceller.com"

End Sub

Function FilterInfo(rngTitle As Range) As String

    Dim objFilter As Filter
    Dim i As Integer
    Dim strField As Variant
    Dim strFilter As String
    strField = Array("월", "팀장", "영업사원", "브랜드", "판매수량", "판매금액")
    On Error Resume Next

    With ActiveSheet.AutoFilter
        If Intersect(rngTitle, .Range) Is Nothing Then Exit Function
        ' 만약 표의 타이틀 부분이 제대로 설정되어 있지 않다면 함수를 빠져 나갑니다.
        ' 이 때 Intersect 메서드를 사용한 점을 눈여겨 보시기 바랍니다.

        For Each objFilter In .Filters
        ' 필터링 된 필드를 돌며 정보들을 strFilter라는 문자열 변수에 차곡차곡 담습니다.

            If Not objFilter.On Then GoTo ET
            strFilter = strFilter & strField(i) & objFilter.Criteria1 & vbCr

            Select Case objFilter.Operator
            ' 사용자 지정 자동 필터가 설정된 경우에 대비해서 아래와 같이 설정합니다.

                Case xlAnd
                    strFilter = Left(strFilter, Len(strFilter) - 1)
                    strFilter = strFilter & " 그리고 " & Mid(objFilter.Criteria2, 2)
                Case xlOr
                    strFilter = Left(strFilter, Len(strFilter) - 1)
                    strFilter = strFilter & " 또는 " & Mid(objFilter.Criteria2, 2)
                Case Else
            End Select

ET:
            i = i + 1
        Next objFilter
    End With

    FilterInfo = strFilter

End Function

많이 응용해 보시고 더 나은 방법을 찾으시거든 꼭 알려주시기 바랍니다.

오늘은 여기까지…

정리 — 필터링 정보 표시 코드 핵심

자주 묻는 질문 (FAQ)

마치며

Click 이벤트로 해결할 수 없는 일도 Calculate 이벤트와 휘발성 함수를 조합하는 약간의 트릭으로 해결할 수 있습니다. 더 나은 방법을 찾으셨다면 꼭 알려 주세요.