이중 유효성검사 설정하기

Step 1: 가계부 서식 파일 살펴보기

어떻습니까? 가계부 시트의 비목 부분으로 셀포인터를 옮겨가면 콤보박스가 하나 나타나는데 이 중에서 아무 항목이나 하나 선택을 하고 오른쪽 셀로 셀포인터를 옮깁니다. 그러면 이 계정과목 셀에도 콤보박스가 하나 나타나기는 하는데 잘 살펴보시면 신기하게도(!) 바로 옆의 셀이 어떤 비목이냐에 따라서 계정과목이 서로 다르게 나타나지요?

즉, 비목으로 급여를 지정하면 계정과목으로 월급, 상여급, 특별 상여금 등의 항목이 리스팅되고, 비목으로 의생활을 선택하면 계정과목으로 옷, 신발, 잡화 등의 항목이 리스팅 됩니다. 다른 비목으로 바꾸었을 때에도 마찬가지로 그에 상응하는 계정들이 주욱 리스팅됩니다. 이와 같은 것을 '이중 유효성 검사'라고 이름 붙여 보았습니다. Exceller가 붙여 본 것이므로 책 같은데 안 나온다고 항의하지 마세요. ^^

Excel의 서식 파일(확장자가 xlt인 파일)들은 대부분 Lock이 걸려 있어서 안타깝게도 소스코드를 볼 수가 없습니다. 그래서 어떤 원리에 의해 이런 일이 가능한 것인지 정확히는 알 수 없지만 아마 제가 사용한 방법에서 크게 벗어나지는 않을 것입니다.

Exceller처럼 맛이 간(^^;) 사람들이나 자신의 소스코드를 100% 공개하고 그것도 모자라 친절한 해설까지 해 드리지, 대개의 경우에는 이 서식파일의 예에서 볼 수 있듯이 핵심코드는 이렇게 Lock을 걸어두고 오픈을 시키지 않습니다.

자, 지금부터 그 작동원리를 하나씩 파헤쳐 보도록 하지요.

Step 2: 이름 정의와 유효성 검사 설정

(1) 먼저, 계정과목 시트에서 보시면 각 계정별로 이름을 정의하였습니다. 화면 좌측 상단의 "이름 상자" 드롭다운 버튼을 눌러 보시면 계정, 계정01, 계정02,… 등의 이름이 나타날 것입니다. 급여와 관련있는 계정과목들은 '계정01', 식비와 연관된 계정과목들은 '계정02', 의생활과 관련있는 계정과목들은 '계정03',… 등 각 계정별 이름을 먼저 정의를 합니다.

그런 다음, 가계부 시트의 비목 부분을 아래로 주욱 선택하신 다음(즉 A열) "데이터- 유효성 검사" 메뉴를 선택합니다.

"제한 대상" 항목에서 "목록"을 선택하고 "원본" 항목에는 "=계정"이라고 입력하고 (인용부호는 빼고) 확인 버튼을 클릭합니다.

Step 3: SelectionChange 이벤트 코딩

(2) 이제 필요한 코딩을 합니다. 단순히 하나의 유효성 검사만을 사용한다면야 굳이 프로그래밍을 하지 않아도 되겠지만 이번 시간의 예제와 같이 조건에 따라 서로 다른 처리결과를 얻고자 한다면 프로그래밍을 반드시 해 주어야 합니다. 아마 Microsoft社 프로그래머들도 가계부 서식파일을 만들 때, 엑셀 본연의 함수나 기능만으로 만든 것이 아니고 프로그래밍을 하였을 것입니다.

(→이렇게 설명을 드렸더니 어떤 분이 지적을 해 오셨네요. "반드시 프로그래밍을 해야만 되는 것은 아니지 않느냐?"고. 가만히 생각을 해보니 그럴 수도 있겠다 하는 생각이 드는군요. Index(), Match() 등의 함수와 수식을 잘 조합하면 말이지요. 좋은 지적, 감사합니다. 연구해 보시기 바랍니다.

(3) VB Editor 창으로 가서(Alt +<F11>) 프로젝트 탐색기에서 Sheet2(가계부) 시트를 더블클릭 합니다(프로젝트 탐색기가 화면상에 없으면 Ctrl + R 키를 누릅니다).

(4) 그러면 코드 입력창이 나타나는데, "이벤트 선택 상자"에서 "SelectionChange" 이벤트를 선택합니다. 이번 프로젝트의 핵심은 바로 SelectionChange라는 이벤트 프로시저를 활용한다는 것입니다.

Worksheet_SelectionChange 이벤트 프로시저는 해당 워크시트 내에서 어떤 변화 (즉, 셀포인터를 이동한다든지 방향키를 누른다든지 해서 어떤 이벤트가 발생하는 경우)가 감지될 경우 해당 코드를 자동으로 실행하는 프로시저 입니다. 여러 상황에 매우 유용하게 사용할 수 있는 이벤트 프로시저이니 잘 기억해 두세요.

(5) 이제 Worksheet_SelectionChange 프로시저에 아래와 같이 코드를 입력합니다.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    '//초장부터 눈에 거슬리는 것이 나왔습니다. 프로스저명 뒤에 얄궂은 문자들이 붙어 있지요?
    '//이것은 프로시저를 실행시킬 때 특정 조건을 주어서 실행토록 하려면 위와 같이 사용하는
    '//것입니다. 이것을 ''매개변수(파라미터)라고 합니다. 즉 프로시저가 실행이 될 때,
    '//Target이라는 Range 오브젝트 변수를 '받아서 함께 실행을 하라는 것이지요.

    '//그렇다면 골치 아프게 왜 프로시저를 시작할 때부터 Range 오브젝트 변수 따위를 받아서
    '//실행을 하느냐? 가계부 시트에서 보신 바와 같이 시트 내의 특정 영역을 선택하게 되면
    '//Worksheet_SelectionChange 이벤트가 발생하게 되는데, 그 시점에서의 해당 셀 자체를
    '//Range 오브젝트 변수로 받는 것입니다. 잘 이해가 안가시면 질문하시기 바랍니다.

    Dim rngCell As Range
    Dim intRow As Integer
    Dim strCostName As String
    '//등장인물이라 할 수 있는 변수들을 선언해 주고…

    On Error Resume Next
    '//On Error Resume Next라는 구문은 '에러가 나타나더라도 잔소리하지 말고 다음으로
    '//넘어가라'는 명령입니다. 일종의 에러 검문소라고 할 수 있습니다.

    If ActiveCell.Column = 2 And ActiveCell.Offset(0, -1) <> "" Then
    '//현재 셀의 컬럼이 2이면 즉 셀포인터가 B열 내에 위치하면서 한간 왼쪽 셀의 값이 공란이
    '//아닐 경우에만 아래의 명령들을 실행하라는 것이지요. 가계부 시트에서 보면, A열에 설정된
    '//유효성 검사에 따라 B열이 변해야 하며, A열에 아무 값도 입력되어 있지 않다면 그대로
    '//프로시저를 종료하도록 하기 위해서 이렇게 조건분기문을 하나 집어 넣었습니다.

        For Each rngCell In Source.Range("계정")
        '//여기서 Source라는 것은 "계정과목" 시트의 Alias(별명) 입니다.
        '//이 부분을 …rngCell In Sheets("계정과목").Range("계정") 이렇게 쓰셔도 될 것입니다.
        '//계정과목 시트의 계정이라는 범위 내의 모든 셀들에 대해서 아래의 문장을 실행합니다.

            If rngCell.Value = Target.Offset(0, -1).Text Then
            '//현재 셀에서 위로 0간, 좌로 1간 이동한 셀의 값이 "계정과목" 시트의 "계정" 범위 내의
            '//셀과 같으면…
                intRow = rngCell.Row - 1
                strCostName = "계정" & Format(intRow, "00")
                Exit For
            End If
        Next rngCell

        With Target.Validation
        '//여기서부터는 유효성 검사 메뉴의 조건을 설정해 주는 부분입니다.
        '//이것을 일일이 손으로 입력해 주어야 하느냐? 천만의 말씀이지요. 매크로 기록기라는
        '//좋은 도구가 있는데 이걸 다 외워서 입력할 필요가 없겠지요. 조건들을 잘 지정해서
        '//매크로를 자동기록한 다음, 약간만 가공해서 복사-붙여넣기를 하세요.
        '//아래의 Formula1 부분만 고치시면 될 것입니다.
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, Formula1:="=" & strCostName
            .IgnoreBlank = True
            .InCellDropdown = True
            .IMEMode = xlIMEModeNoControl
            .ShowInput = True
            .ShowError = False
        End With
    End If
End Sub

마무리

이렇게 하시면 됩니다. 좀 복잡하지요?

오늘 설명드린 내용은 논리적으로 약간 까다롭지 코딩하는 것은 그다지 어렵지 않을 않을 것입니다. 단지 어렵게 보일 따름이지요. 어려운 것과 어려워 보이는 것은 엄연히 다른 것입니다.

하시다가 의문사항이 있거나 이것보다 더 빨리 실행시킬 수 있는 방법이 있는 분은 연락주시기 바랍니다.

오늘은 여기까지…

정리 — 이중 유효성 검사 핵심

자주 묻는 질문 (FAQ)

마치며

어려운 것과 어려워 보이는 것은 엄연히 다릅니다. 코딩 자체는 그다지 어렵지 않습니다.