엑셀 VBA로 다중 종속 드롭다운 목록 만들기(3가지 방법)

MS 엑셀에서 다중 종속 드롭다운 목록을 만드는 일은 늘 까다로운 작업으로 여겨져 왔습니다. 두 개의 드롭다운 목록 사이에 종속 관계나 연관성이 있을 때 이러한 목록이 필요합니다. 보통은 다양한 수식이나 엑셀의 옵션 설정을 통해 만들지만, 엑셀 VBA 코드를 활용하면 훨씬 쉽게 여러 개의 드롭다운 목록을 구성할 수 있습니다. 이 글에서는 VBA 코드를 사용해 다중 드롭다운 목록을 만드는 여러 가지 방법을 소개합니다.

함께 읽으면 좋은 글: 엑셀에서 드롭다운 목록 만드는 방법(독립형 및 종속형)

엑셀에서 종속 드롭다운 목록이란?

본격적인 작업에 앞서, 엑셀에서 '종속 드롭다운 목록'이 무엇인지 먼저 짚고 넘어가겠습니다. 두 개 이상의 드롭다운 목록 사이에 종속 관계가 성립할 때, 우리는 이를 종속 드롭다운 목록이라고 부릅니다. 아래 그림을 보면 종속 드롭다운 목록의 개념을 명확하게 이해할 수 있습니다.

그림에서 볼 수 있듯이 'Category(분류)'와 'Food(식품)'라는 두 개의 드롭다운 목록은 서로 완전히 종속된 관계입니다. 분류 선택에 따라 식품 목록이 결정되는 방식으로, 바로 이것이 다중 계단식(cascading) 드롭다운 목록이 작동하는 원리입니다.

함께 읽으면 좋은 글: 엑셀에서 동적 종속 드롭다운 목록 만드는 방법

VBA로 다중 종속 드롭다운 목록 만드는 3가지 방법

1. VBA로 드롭다운 목록에서 다중 선택 허용하기

'프로젝트 이름'과 '프로젝트 멤버'라는 두 개의 목록이 있다고 가정해 보겠습니다. 각 프로젝트에 드롭다운 목록을 이용해 한 명 또는 여러 명의 멤버를 배정하려는 상황입니다.

1단계: 개발 도구(Developer) 탭으로 이동한 후 Visual Basic을 엽니다(단축키 Alt + F11).

2단계: VBAProject 메뉴에서 해당 워크시트를 선택합니다.

3단계: VBA 편집기에 아래 코드를 입력합니다.

코드:

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim Old_value As String
 Dim New_value As String
 Application.EnableEvents = True
 On Error GoTo Exitsub
 If Not Intersect(Target, Range("C4:C11")) Is Nothing Then
   If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
      GoTo Exitsub
   Else: If Target.Value = "" Then GoTo Exitsub Else
      Application.EnableEvents = False
      New_value = Target.Value
      Application.Undo
      Old_value = Target.Value
         If Old_value = "" Then
            Target.Value = New_value
         Else
            If InStr(1, Old_value, New_value) = 0 Then
               Target.Value = Old_value & ", " & New_value
         Else:
            Target.Value = Old_value
         End If
      End If
   End If
 End If
 Application.EnableEvents = True
Exitsub:
 Application.EnableEvents = True
End Sub

4단계: 이제 '프로젝트 멤버' 열에서 여러 이름을 선택합니다.

5단계: 모든 셀에서 드롭다운 목록을 통한 다중 선택이 가능해집니다.

함께 읽으면 좋은 글: 엑셀에서 드롭다운 목록으로 다중 선택하는 방법

2. VBA로 다중 종속 드롭다운 목록 만들기

채소, 과일, 유제품 등 다양한 분류의 식품 데이터가 있다고 가정해 보겠습니다. 이제 분류에 따라 식품 항목을 검색하고 싶습니다. 예를 들어 분류를 'Fruits(과일)'로 선택하면 식품 열에는 Raspberry, Apricot, Peach, Mango 같은 항목만 표시되어야 합니다. 즉, 식품 항목은 선택한 분류에 따라 달라지며, Category와 Food 사이에는 종속 관계가 존재합니다.

1단계: 방법 1의 1단계와 2단계와 동일한 절차로 VBA 편집기를 연 뒤, 아래 코드를 입력합니다.

코드:

채소 드롭다운 목록 생성:

Sub Vegetable_List()
 
 Range("C4:C6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
 Formula1:="=Vegetable_List"
 
End Sub

과일 드롭다운 목록 생성:

Sub Fruit_List()
 
 Range("C4:C6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
 Formula1:="=Fruits_list"
 
End Sub

유제품 드롭다운 목록 생성:

Sub Dairy_List()
 
 Range("C4:C6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
 Formula1:="=Dairy_Product_List"
 
End Sub

이 부분에서는 각 식품 항목별 목록을 만들어 드롭다운 목록으로 저장합니다. 이 목록들은 C4:C6 범위에서 사용할 수 있습니다.

2단계: 이제 B4:B6 범위를 위한 메인 함수를 작성합니다.

코드:

Private Sub Worksheet_Change(ByVal Target As Range)
  Range("B4:B6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
  Formula1:="Vegetable_List,Fruits_list,Dairy_Product_List"
  If Range("B4:B6").Value = "Vegetable_List" Then
   Call Vegetable_List
  ElseIf Range("B4:B6").Value = "Fruits_list" Then
  Call Fruit_List
  ElseIf Range("B4:B6").Value = "Dairy_Product_List" Then
  Call Dairy_List
  Else
  End If

코드 설명

• 먼저 B4:B6 범위에 분류(category) 목록을 새로 만듭니다. 이 목록에는 식품 분류명이 담깁니다.
• 그다음 목록의 값을 확인하여 항목에 따라 분류합니다. 이때 IF ELSE 문이 사용됩니다.
• 일치하는 이름을 찾으면 CallBack 방식으로 해당 목록 생성 함수를 호출합니다. 예를 들면 다음과 같습니다.

If Range("B4:B6").Value = "Vegetable_List" Then

Call Vegetable_List

• 여기서 셀 값이 Vegetable_List 텍스트와 일치하면, Vegetable_List 함수를 호출해 채소 목록을 생성하고 표시합니다.

완성된 전체 코드는 다음과 같습니다.

3단계: 이제 워크시트로 돌아가 드롭다운 목록에서 원하는 분류를 선택합니다.

4단계: 그러면 Food(식품) 열에 해당 분류에 속한 항목들이 표시됩니다.

5단계: 최종 결과는 다음과 같습니다.

3. VBA로 다중 종속 드롭다운 목록 초기화하기

앞선 섹션에서는 엑셀에서 서로 연관된 목록을 가져오는 방법만 살펴보았습니다. 하지만 때로는 잘못 매칭된 선택값이 자동으로 삭제되지 않는 경우가 발생합니다. 이런 문제를 방지하기 위해 수식을 활용할 수도 있습니다.

또 다른 방법은 매크로를 사용해 첫 번째 드롭다운에서 선택이 바뀔 때 종속 셀을 자동으로 비우는 것입니다. 이렇게 하면 서로 맞지 않는 선택 조합을 미리 차단할 수 있습니다.

1단계: 방법 1의 1단계와 2단계와 동일한 절차로 VBA 편집기를 연 뒤, 아래 코드를 입력합니다.

코드:

Private Sub Worksheet_Change(ByVal Target As Range)
 On Error Resume Next
If Target.Column = 2 Then
  If Target.Validation.Type = 3 Then
   Application.EnableEvents = False
   Target.Offset(0, 1).ClearContents
  End If
End If

exitHandler:
  Application.EnableEvents = True
  Exit Sub
End Sub

2단계: 이제 Food(식품) 열에서 임의의 항목을 선택한 후, Category(분류)에서 다른 분류를 선택해 보고 어떻게 되는지 확인합니다.

첫 번째 선택

두 번째 선택

최종 결과

함께 읽으면 좋은 글: 엑셀에서 드롭다운 목록 제거하는 방법

주의사항

결론

지금까지 엑셀 VBA로 다중 종속 드롭다운 목록을 만들고 활용하는 몇 가지 방법을 살펴보았습니다. 각 방법을 실제 예제와 함께 소개했지만, 이 외에도 다양한 변형이 가능합니다. 사용된 함수들의 기본 원리도 함께 설명했습니다. 만약 이 외에 다른 방법을 알고 계시다면 언제든지 공유해 주세요.

추천 학습 자료

• 엑셀에서 여러 열에 드롭다운 목록 만드는 방법(3가지)
• 선택 항목에 따라 달라지는 엑셀 드롭다운 목록
• IF 문으로 엑셀 드롭다운 목록 만드는 방법
• 다른 시트의 데이터로 엑셀 드롭다운 목록 만들기(2가지 방법)
• 엑셀에서 드롭다운 목록 편집하기(4가지 기본 방법)
• 엑셀에서 VLOOKUP과 드롭다운 목록 함께 사용하기