콤보박스를 통한 자료추출

Step 1: 자료 추출 프로시저 작성하기

(1) VB Editor 상태에서 모듈시트를 하나 삽입하고 아래의 코드를 입력합니다.

Sub ExtractDept()
    Dim i As Integer
    Dim intCount As Integer
    Dim strDept As String
    Dim Msg As String
    Dim sht As Worksheet
    Dim rngCell As Range
    Dim strYesNo As String
    Application.DisplayAlerts = False

    strDept = Range("DeptName")
    '//Application.DisplayAlerts=False라고 설정을 하면 시트 삭제 등의 작업을 할 때,
    '//"이 작업은 취소할 수 없습니다. 계속 할까요? 말까요? 어쩔까요?…"
    '//등의 메시지가 나타나지 않게 됩니다.

    '//셀에 이름을 정의한 다음, 그 내용을 strDept라는 변수에 담아둡니다.
    '//범위에 이름을 정의하고 사용하는 것은 좋은 습관이니까 반드시 몸에 붙이시기를…

    For Each sht In Sheets
    '//워크북 내에 있는 모든 시트들을 검사하여 같은 이름의 시트가 이미 있으면 지우고 다시
    '//작성합니다(물론 사용자가 다시 만들고자 할 때만…).

        If sht.Name = strDept & "Sheet" Then
            Msg = "같은 시트가 이미 있습니다" & vbCr
            Msg = Msg & "지우고 다시 만들까요?"
            strYesNo = MsgBox(Msg, vbYesNo, "동일 시트 발견//Exceller")
            If strYesNo = vbNo Then
                Sheets(strDept & "Sheet").Select
                Exit Sub
            End If
            sht.Delete
        End If
    Next sht

    Set sht = Worksheets.Add
    sht.Name = strDept & "Sheet"
    Range("Title").Copy
    Sheets(strDept & "Sheet").Range("a1").Select
    ActiveSheet.Paste
    '//워크시트를 한장 삽입하고 이름을 "부서명" + Sheet로 지정합니다. 그리고 나서 Source
    '//시트에 있는 타이틀 부분을 새로 삽입한 워크시트로 복사합니다.

    Do While Range("start").Offset(i, 1) <> ""
        If Range("start").Offset(i, 1) = strDept Then
            Range(Range("start").Offset(i, 0), Range("start").Offset(i, 4)).Copy
            intCount = intCount + 1
            sht.Range("a1").Offset(intCount, 0).Select
            sht.Paste
        End If
        i = i + 1
        '//Do~Loop 반복문을 통해 소스시트의 부서명이 콤보박스에서 선택한 부서명과 같을 경우,
        '//새로 삽입한 시트에 차례로 복사해 넣습니다.
    Loop

    Columns.AutoFit
    ActiveWindow.DisplayGridlines = False
    Range("a1:a3").EntireRow.Insert
    Range("a1").Select
    With Selection
        .Value = "2000년 " & strDept & " 장비 구매내역"
        .Font.Size = 20
    End With
    Range("a3").Select
    Call MakeButton
    '//작업이 다 끝났으면 열폭 조정, 타이틀 삽입 등의 작업을 합니다. 그리고 나서 MakeButton
    '//이라는 또다른 프로시저를 호출합니다. 이 때 Call이라는 명령어는 쓰지 않아도 되지만
    '//예전 BASIC(Visual Basic말고 그냥 BASIC…) 때 하던 버릇이 남아 있어서 그냥 씁니다.
    '//Exceller의 개인적인 견해입니다만, 이렇게 해 주는 것이 덜 헷갈리더군요. ^^

End Sub

Step 2: 돌아가기 버튼 만들기

(2) 위에서 호출한 MakeButton 프로시저를 작성합니다. 이 프로시저는 선택한 부서의 자료추출이 끝난 다음에 종전 위치로 돌아가는 버튼을 만드는 것입니다.

Sub MakeButton()
    With ActiveCell
        ActiveSheet.Buttons.Add(.Left, .Top, .Width * 2, .Height).Select
    End With
    '//현재 셀 포인터가 위치한 곳에 버튼을 하나 그려 넣습니다.

    With Selection
        .Caption = "<<돌아가기"
        With .Characters.Font
            .Size = 10
            .ColorIndex = 3
        End With
        .OnAction = "GoBack"
    End With
    '//삽입한 버튼에 대해 Caption과 글자 크기, 글자 색상 등을 설정하고 OnAction 속성을
    '//지정합니다. OnAction 속성은 해당 개체를 클릭했을 때 실행되는 매크로를 설정하는
    '//속성입니다. 따라서 이 버튼을 클릭하면 GoBack이라는 또다른 프로시저를 실행하게
    '//되는 것이지요.

    Range("a5").Select
    ActiveWindow.FreezePanes = True
    MsgBox "작업이 끝났습니다", vbInformation, "작업 종료//Exceller"
End Sub

버튼에 연결된 GoBack 프로시저는 원래의 Preface 시트로 돌아가는 역할을 합니다.

Sub GoBack()
    Sheets("Preface").Select
End Sub

Step 3: 콤보박스 삽입하고 매크로 연결하기

프로시저 작성은 끝났고… 이제 콤보박스를 하나 삽입하고 VBA 코드를 콤보박스에 연결해 주는 작업을 해야 합니다.

(3) "보기-도구 모음-양식" 메뉴를 선택하고 "콤보 상자" 아이콘을 누른 다음, 워크시트에 콤보박스를 하나 그려 넣습니다. 이 때 Alt 키를 누르고 마우스 왼쪽 버튼을 눌러 드래그 하면 셀 크기에 꼭맞는 콤보박스가 그려집니다. 이것은 다른 개체를 그릴 때에도 마찬가지입니다.

(4) (3)에서 그린 콤보박스를 마우스 오른쪽 버튼으로 클릭하면 [그림 1]과 같은 단축 메뉴가 나타나는데 "매크로 지정" 메뉴를 클릭합니다.

아이엑셀러

(5) [그림 2]와 같이 좀전에 작성한 ExtractDept 프로시저를 선택하고 "확인" 버튼을 클릭하면 끝!

아이엑셀러

여기서 한 가지 주의하실 점은 (3) 단계에서 반드시 "양식" 도구모음에 있는 "콤보박스"를 선택한다는 점입니다. "양식" 도구모음과 아주 비슷하게 생긴 "컨트롤" 도구 모음에 있는 "콤보박스"를 선택하면 안됩니다. 아직도 이 두 가지를 혼동해서 질문을 하시는 분이 자주 계시더군요.

마무리

많이 응용해 보시기 바랍니다.

다음 시간에…

정리 — 콤보박스 자료 추출 핵심

자주 묻는 질문 (FAQ)

마치며

콤보박스와 이름 정의를 활용하면 클릭 한 번으로 원하는 부서의 자료만 따로 추출할 수 있습니다.