콤보박스 활용 테크닉2

Step 1: 예제 데이터와 이름 정의

"검색 방법"(년도별/분기별/월별)과 그에 해당하는 조건을 콤보 박스로 선택하고 요약시트를 만드는 예제입니다. 아래 데이터(A33:D81)를 사용하며 다음과 같은 이름이 정의되어 있습니다.

Step 2: 공통 변수와 콤보 박스 값 읽기 (DefineVariableName)

Option Explicit
Dim cmbSearch As DropDown, cmbYear As DropDown, cmbWhen As DropDown
Dim strSearch As String
'여러 프로시저에서 공통적으로 사용되는 변수들을 프로시저 외부에 선언합니다.

Sub DefineVariableName()
    With Worksheets("Preface")
        Set cmbSearch = .DropDowns("cmbSearch")
        Set cmbYear = .DropDowns("cmbYear")
        Set cmbWhen = .DropDowns("cmbWhen")
    End With
    With cmbSearch
        strSearch = .List(.ListIndex)
    End With
    '''마찬가지로 각 프로시저에서 공통적으로 사용되는 콤보박스의 내용을 읽어오는 부분도
    '''별도의 프로시저로 나눕니다.
End Sub

Step 3: 검색 방법에 따라 화면 바꾸기 (FilterData)

Sub FilterData()
    DefineVariableName
    cmbYear.ListFillRange = "년도"
    '''cmbYear 콤보 박스의 내용을 "년도"라는 범위명 내에 있는 값으로 채웁니다.
    '''cmbSearch 콤보 박스에서 선택된 값에 따라 Select Case 분기문을 실행합니다.

    Select Case strSearch
        Case "년도별"
            With [Criteria]
                .ClearContents
                cmbWhen.Visible = False
                .Interior.ColorIndex = 19
            End With
            '''cmbSearch 콤보 박스에서 선택된 값이 "년도별"일 경우, cmbWhen 콤보 박스를
            '''숨기고 셀 내부 ColorIndex 속성값을 19(연한 미색)로 만듭니다. 왜 이렇게 하는
            '''것일까요? 년도별 데이터를 선택할 경우, 분기별 또는 월별 이라는 콤보 박스는
            '''불필요할 것이므로 일시적으로 화면상에서 숨기기 위한 것입니다.

        Case "분기별"
            With cmbWhen
                .ListFillRange = "분기"
                .Visible = True
            End With
            With [Criteria]
                .Value = "분기별"
                .Interior.ColorIndex = 16
            End With

        Case "월별"
            With cmbWhen
                .ListFillRange = "월"
                .Visible = True
            End With
            With [Criteria]
                .Value = "월별"
                .Interior.ColorIndex = 16
            End With
        End Select
End Sub

Step 4: 요약시트 만들기 (MakeFilterSheet)

이제 "요약시트 만들기" 버튼에 연결된 코드를 보도록 하지요. 코드가 좀 깁니다. 잘 따라 오세요.

Sub MakeFilterSheet()
    Dim rngCell As Range, rngDate As Range
    Dim strYear As String, strWhen As String, Msg As String
    Dim i As Integer
    Dim shtFilter As Worksheet
    Set shtFilter = Worksheets("FilterSheet")
    Set rngDate = [날짜]
    Msg = "해당되는 데이터가 없습니다"
    '''등장인물과 배역을 설정해 주었습니다.

    DefineVariableName
    With cmbYear
        strYear = .List(.ListIndex)
    End With
    '''필요한 변수들을 외부 프로시저에 한번 선언해 놓고 여러 프로시저에서 불러다
    '''사용합니다. 컴퓨터 프로그래밍이든 현실 세계에서든 중복되는 요소가 많다는 것은
    '''곧 비효율적인 것입니다.

    Select Case strSearch
        Case "년도별"
            For Each rngCell In rngDate
                If Year(rngCell) = strYear Then
                    If i = 0 Then MakeSheet
                    rngCell.EntireRow.Copy
                    Worksheets("FilterSheet").Paste Destination:=Worksheets("FilterSheet").Cells(i + 2, 1)
                    i = i + 1
                End If
            Next rngCell
            If i = 0 Then
                MsgBox Msg, , "작업 완료//By Exceller"
                Exit Sub
            End If
            MakeSum strYear & "년 ", i

        Case "분기별"
            With cmbWhen
                strWhen = .List(.ListIndex)
            End With

            For Each rngCell In rngDate
                If Year(rngCell) = strYear Then
                    If strWhen = Choose(Application.WorksheetFunction.RoundUp(Month(rngCell) / 3, 0), "1분기", "2분기", "3분기", "4분기") Then
                    '''분기별 데이터를 구하기 위해 어떤 수식을 사용했나 잘 살펴보세요.
                    '''워크시트 함수인 RoundUp 함수를 사용했지요? 예를 들어 1분기라는 것은
                    '''3으로 나누었을 때 1이하가 될 것입니다. 이것을 무조건 올림처리하면 1, 즉
                    '''1분기가 되는 것이지요.

                        If i = 0 Then MakeSheet
                        rngCell.EntireRow.Copy
                        Worksheets("FilterSheet").Paste Destination:=Worksheets("FilterSheet").Cells(i + 2, 1)
                        i = i + 1
                    End If
                End If
            Next rngCell
            If i = 0 Then
                MsgBox Msg, , "작업 완료//By Exceller"
                Exit Sub
            End If
            MakeSum strYear & "년 " & strWhen, i

        Case "월별"
            With cmbWhen
                strWhen = .List(.ListIndex)
            End With

            For Each rngCell In rngDate
                If Year(rngCell) = strYear Then
                    If strWhen = Month(rngCell) Then
                        If i = 0 Then MakeSheet
                        rngCell.EntireRow.Copy
                        Worksheets("FilterSheet").Paste Destination:=Worksheets("FilterSheet").Cells(i + 2, 1)
                        i = i + 1
                    End If
                End If
            Next rngCell
            If i = 0 Then
                MsgBox Msg, , "작업 완료//By Exceller"
                Exit Sub
            End If
            MakeSum strYear & "년 " & strWhen & "월 ", i

        End Select

End Sub

Step 5: 시트 만들기 (MakeSheet)

위의 MakeFilterSheet 프로시저에서 호출한 외부 프로시저입니다. 검색한 결과를 표시할 시트가 있는 지 확인한 다음 시트를 만드는 과정입니다.

Sub MakeSheet()
    Dim shtSheet As Worksheet

    For Each shtSheet In ThisWorkbook.Sheets
        If shtSheet.Name = "FilterSheet" Then
            Application.DisplayAlerts = False
            MsgBox "동일한 시트가 이미 있으므로 지우겠습니다", , "동일시트 삭제//By Exceller"
            shtSheet.Delete
        End If
    Next shtSheet

    With Worksheets.Add
        .Name = "FilterSheet"
        [Title].Copy
        .Paste .[A1]
    End With
    Application.DisplayAlerts = True
End Sub

Step 6: 합계 구하기 (MakeSum)

마지막으로, 추출한 데이터의 마지막 부분에 합계를 구하는 프로시저입니다.

Sub MakeSum(strX As String, intX As Integer)
    Dim i As Integer
    ActiveSheet.UsedRange.Columns.AutoFit
    With ActiveSheet.[A1]
        For i = 2 To 3
            With .Offset(intX + 1, i)
                .FormulaR1C1 = "=sum(r[-1]c:r[" & -intX & "]c)"
                .Interior.ColorIndex = 3
                .Font.ColorIndex = 2
            End With
        Next i
        .Offset(intX + 1) = strX & " 합계"
    End With

End Sub

마무리

코드가 좀 길어 졌습니다. 한번씩 해 보시고 이해가 안되는 부분이 있으면 질문하세요.

오늘은 여기까지…

정리 — 콤보 박스 조건 추출 핵심

자주 묻는 질문 (FAQ)

마치며

공통 변수와 프로시저 분리를 활용하면 콤보 박스로 조건을 고르고 요약시트를 만드는 긴 코드도 깔끔하게 구성할 수 있습니다.