이럴 때 필요해요

표를 눌렀을때 콤보박스에서 셀마다 다른목록을 불러오고싶을

이렇게 사용해요

아래의 표에서 비목부분을 선택하신 다음, 계정과목부분을  눌러 나타 나는 결과를 잘 살펴 보시기 바랍니다.

어떻습니까? 비목 부분의 셀을 선택하면 콤보박스가 하나 나타 나는데, 이 중에서 아무 항목이나 하나 선택을 하고 오른쪽 

셀로 셀포인터를 옮깁니다. 그러면, 이 계정과목 셀에도 콤보박스가 하나 나타 나기는 하는데 잘 살펴 보시면 신기하게도 

바로 옆의 셀이 어떤 비목이냐에 따라서 계정과목이 서로 다르게 나타나지요? ^^

즉, 예를 들어 비목으로 급여를 지정하면 계정과목으로 월급, 상여급, 특별 상여금… 등의 항목이 리스팅되고, 비목을 

식비로 선택하면 계정과목 역시 주식, 부식, 외식… 등의 항목으로 변경되어 리스팅 됩니다. 

다른 비목으로 바꾸었을 때에도 마찬가지로 그에 상응하는 계정들이 주욱 리스팅됩니다. 

따라서, 이것을 '이중 유효성 검사'라고 나름대로 이름 붙여 보았습니다. ^^

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

(1) 먼저, 계정과목 시트에서 보시면 각 계정별로 [이름]을 정의하였습니다.

화면 좌측 상단의 "이름 상자" 드롭다운 버튼을 눌러 보시면 아래의 그림과 같이 계정, 계정01, 계정02,…등의 이름들이 

나타 날 것입니다. 급여와 관련있는 계정과목들은 '계정01', 식비와 연관된 계정과목들은 '계정02', 의생활과 관련있는 

계정과목들은 '계정03',… 등으로 각 계정별 이름을 먼저 정의를 합니다.

그런 다음, 위 표의 비목 부분(즉, C33:C39)을 아래로 주욱 선택하신 다음 [데이터]-[유효성 검사] 메뉴를 선택합니다.

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

(2) 이제 필요한 코딩을 합니다. 단순히 하나의 유효성 검사만을 사용한다면야 굳이 프로그래밍을 하지 않아도 되겠지만 

이번 시간의 예제와 같이 조건에 따라 서로 다른 처리결과를 얻고자 한다면 반드시 프로그래밍을 해 주어야 합니다. 

아마,  Microsoft社의 프로그래머들도 가계부 서식파일을 만들 때, 엑셀 본연의 함수나 기능만으로 만든 것이 아니고 프로그래밍을 하였을 것입니다.

(3) VB Editor 창으로 가서(Alt +) 프로젝트 탐색기에서 Sheet1(Preface) 시트를 더블클릭 합니다.

(프로젝트 탐색기가 화면상에 없으면 Ctrl + R 키를 누릅니다)

(4) 그러면, 코드 입력창이 나타나는데, 이벤트 선택 상자에서 SelectionChange 이벤트를 선택합니다.

 이번 프로젝트의 핵심은 바로, 이 SelectionChange라는 이벤트 프로시저를 활용한다는 것입니다.

Worksheet_SelectionChange이벤트는 앞에서 몇 차례 소개해 드렸던 Worksheet_Change이벤트와 같이

선택 영역(Target)의 값이 변경되면 발생하는 것이 아니고, 해당 워크시트 내에서 특정 셀을 선택(Select)하거나 

또는 방향키를 누르는 등, 즉 선택 영역을 변경할 때 이벤트가 발생하게 되며, 또한 이 때 해당 코드를 자동으로 실행하게 됩니다. 

(5) 먼저, 프로젝트 탐색기창에서 Sheet1(Preface)의 Worksheet_SelectionChange 프로시저에 기록된 내용을 천천히 살펴 보고 오시기 바랍니다. 

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    '//초장부터 눈에 거슬리는 것이 나왔습니다. 프로스저명 뒤에 얄궂은 문자들이 붙어 있지요?

    '//이것은 프로시저를 실행시킬 때 특정 조건을 주어서 실행토록 하기 위해 위와 같이 사용하는 것입니다.

    '//이것을 매개변수(파라미터)라고 합니다. 즉, 프로시저가 실행이 될 때, Target이라는 Range 오브젝트 변수를 

    '//받아서 함께 실행을 하라는 것이지요.

    '//그렇다면, 골치 아프게 왜 프로시저를 시작할 때부터 Range 오브젝트 변수 따위를 받아서 실행을 하느냐?

    '//앞에서 설명드린 바와 같이 시트 내의 특정 영역을 선택하게 되면, Worksheet_SelectionChange 이벤트가

    '//발생하게 되는데, 발생 즉시 해당 셀(즉, Target) 자체를 Range 오브젝트 변수로 받기 위해서입니다.

    '//잘 이해가 안 가시면 별도로 질문하시기 바랍니다.

    Dim rngCell As Range

    Dim intRow As Integer

    Dim strCostName As String

    '//먼저, 등장인물이라 할 수 있는 변수들을 선언해 주고…

    On Error Resume Next

    '//On Error Resume Next 라는 구문에 대해서는 앞에서 설명드렸었죠?

    '//다시 한번 더 설명드리면, 에러가 나타 나더라도 잔소리 하지말고 그냥 넘어 가라 는 뜻입니다. ^^

    If ActiveCell.Column = 4 Then

    '//현재 셀(Targe)의 열번호(Column)가 4이면, 즉 셀 포인터가 D열 내에 있으면 아래의 명령들을 실행하라는 것이지요.

    '//앞에서도 살펴 보신 바와 같이, 비목부분에 설정된유효성 검사에 따라 계정과목부분이 변해야 하며, 또한 선택된 셀이

    '//비목부분의 영역이 아닐 경우, 그대로 프로시저를 종료하도록 하기 위해서 이렇게 조건분기문을 지정하는 것입니다.

        For Each rngCell In [계정]

        '//계정과목 시트의 "계정"이라고 [이름]정의된 범위 내의 모든 셀들에 대해서 아래의 문장을 실행합니다.

            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 Selection.Validation

        '//여기서부터는 유효성 검사 메뉴의 조건을 설정해 주는 부분입니다.

        '//이것을 일일이 손으로 입력해 주어야 하느냐? 천만의 말씀이지요. ^^  매크로 기록기라는 좋은 도구가 있는데

        '//이걸, 다 외워서 입력할 필요가 없겠지요. 조건들을 잘 지정해서 매크로를 자동기록한 다음, 약간만 가공해서

        '//[복사]-[붙여넣기]를 하시면 됩니다.^^

        '//아래의 빨간색 부분만 고치시면 될 것입니다.

            .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

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