Excel VBA로 다중 선택 가능한 데이터 유효성 검사 드롭다운 목록 만들기 (3가지 방법)

이 글에서는 Excel의 데이터 유효성 검사 드롭다운 목록에서 여러 항목을 동시에 선택할 수 있도록 설정하는 방법을 알아봅니다. 기본적으로 데이터 유효성 검사를 사용하면 목록에서 단 하나의 항목만 선택할 수 있다는 제약이 있습니다. 하지만 드롭다운 메뉴에서 여러 값을 한 번에 입력하고 싶은 경우가 종종 생기죠.

이 튜토리얼에서는 VBA 매크로를 활용해 이러한 제한을 해결하는 방법을 실전 예제와 함께 단계별로 설명합니다. 연습용 통합 문서는 아래에서 다운로드할 수 있습니다.

다중 선택 데이터 유효성 검사 드롭다운 목록 만들기 – 3가지 예제

VBA 매크로를 적용해 다중 선택이 가능한 드롭다운 목록을 만드는 과정은 다소 복잡할 수 있습니다. 그래서 이해를 돕기 위해 3가지 서로 다른 시나리오로 나누어 차근차근 살펴보겠습니다.

1. 하나의 셀에 여러 항목을 쉼표로 구분해 입력하기

첫 번째 예제에서는 국가 이름 4개(미국, 영국, 캐나다, 멕시코)로 구성된 데이터셋을 사용합니다. 일반적인 데이터 유효성 검사라면 한 셀에 국가 이름을 하나만 입력할 수 있지만, 이번에는 여러 국가명을 하나의 셀에 함께 입력하는 것이 목표입니다.

그럼 구체적인 작업 순서를 확인해 보겠습니다.

단계별 진행:

• 먼저 셀 범위 D4:D8을 선택하고 이름 범위를 'dv_list_0'으로 지정합니다.

• 다음으로 셀 범위 B5:B8을 선택하고 이름 범위를 'Country_Range'로 지정합니다.

• 상단 메뉴에서 데이터 > 데이터 도구 > 데이터 유효성 검사 > 데이터 유효성 검사로 이동합니다.

• '데이터 유효성 검사' 대화 상자가 열리면 제한 대상에서 '목록'을 선택하고, 원본 입력란에 아래 수식을 입력한 뒤 확인을 클릭합니다.

• 설정이 완료되면 선택한 셀 오른쪽에 드롭다운 화살표가 나타납니다.

• 이제 활성 시트 탭에서 마우스 오른쪽 버튼을 클릭하고 '코드 보기(View Code)' 옵션을 선택합니다.

• 열린 빈 VBA 모듈에 아래 코드를 붙여넣습니다.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Value_Old As String
Dim Value_New As String
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
If Not Intersect(Target, ActiveSheet.Range("Country_Range")) Is Nothing Then
Application.EnableEvents = False
Value_New = Target.Value
On Error Resume Next
Application.Undo
On Error GoTo 0
Value_Old = Target.Value
If InStr(Value_Old, Value_New) Then
If InStr(Value_Old, ",") Then
If InStr(Value_Old, ", " & Value_New) Then
Target.Value = Replace(Value_Old, ", " & Value_New, "")
Else
Target.Value = Replace(Value_Old, Value_New & ", ", "")
End If
Else
Target.Value = ""
End If
Else
If Value_Old = "" Then
Target.Value = Value_New
Else
If Value_New = "" Then
Target.Value = ""
Else
If InStr(Target.Value, Value_New) = 0 Then
Target.Value = Value_Old & ", " & Value_New
End If
End If
End If
End If
Application.EnableEvents = True
Else
Exit Sub
End If
End Sub

• F5 키를 누르거나 실행 버튼을 클릭해 코드를 실행합니다.

• 매크로 이름을 지정하고 실행(Run)을 누릅니다. 이 예제에서 매크로 이름은 'VBA'입니다.

• 이후 셀 B5의 드롭다운 메뉴에서 'USA'를 선택하면 해당 국가명이 B5 셀에 자동으로 입력됩니다.

• 같은 방식으로 'Canada'와 'Mexico'도 차례로 선택하면, 드롭다운에서 고른 모든 값이 하나의 셀에 쉼표로 구분되어 저장되는 것을 확인할 수 있습니다.

더 읽어보기: Excel 데이터 유효성 검사 드롭다운 목록 만들기 (8가지 방법)

2. 선택한 값들을 인접 열에 자동으로 분리 입력하기

두 번째 예제에서는 드롭다운 목록에서 선택한 항목들이 인접 열에 차례대로 자동 입력되도록 설정합니다. 드롭다운에서 항목을 하나씩 선택할 때마다 옆 열로 값이 이동하는 방식입니다. 앞선 예제와 동일한 데이터셋을 사용합니다.

작업 절차는 다음과 같습니다.

단계별 진행:

• 우선 셀 D5를 선택하고 앞서와 같은 방법으로 데이터 유효성 검사 드롭다운을 만듭니다. 이때 원본 범위는 B5:B8을 사용합니다.

• 다음으로 활성 시트 탭에서 오른쪽 버튼을 클릭하고 '코드 보기(View Code)'를 선택합니다.

• 새로 열린 빈 VBA 모듈에 아래 코드를 작성합니다.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler
Dim DV_Range As Range
Dim Col_i As Integer
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set DV_Range = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If DV_Range Is Nothing Then GoTo exitHandler
If Intersect(Target, DV_Range) Is Nothing Then
Else
Application.EnableEvents = False
If Target.Column = 4 Then
If Target.Value = "" Then GoTo exitHandler
If Target.Validation.Value = True Then
Col_i = Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 1
Cells(Target.Row, Col_i).Value = Target.Value
Else
MsgBox "Invalid entry"
Target.Activate
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub

• F5 키 또는 실행 아이콘을 눌러 코드를 실행합니다.

• 매크로(Macros) 대화 상자가 열리면 매크로 이름을 'VBA1'으로 지정하고 실행을 클릭합니다.

• 이제 셀 D5의 드롭다운에서 'USA'를 선택합니다.

• 선택 즉시 'USA'가 인접 열 E5 셀로 자동 이동해 입력됩니다.

• 마찬가지로 'Canada'와 'Mexico'를 계속 선택하면 각 값이 인접 열에 하나씩 배치됩니다.

더 읽어보기: Excel VBA 데이터 유효성 검사 목록 기본값 설정 (매크로 및 사용자 폼)

관련 추천 글:

• Excel 데이터 유효성 검사 드롭다운 자동 완성 (2가지 방법)
• 테이블로 데이터 유효성 검사 목록 만들기 (3가지 방법)
• 한 셀에 여러 데이터 유효성 검사 적용하기 (3가지 예제)
• 데이터 유효성 검사 목록에서 공백 제거하기 (5가지 방법)
• 사용자 지정 수식으로 영숫자만 허용하는 데이터 유효성 검사

3. 선택한 값들을 개별 행에 세로로 정렬하기

세 번째 예제는 두 번째 예제와 매우 유사하지만, 결과가 다릅니다. 두 번째 예제에서는 값이 옆 열(가로)로 배치되었다면, 이번에는 아래 행(세로)으로 하나씩 쌓이는 방식입니다. 마찬가지로 기존 데이터셋을 활용합니다.

진행 순서를 살펴보겠습니다.

단계별 진행:

• 먼저 셀 D5를 선택하고 앞선 방식과 동일하게 데이터 유효성 검사 드롭다운을 생성합니다. 원본 값은 B5:B8 범위를 사용합니다.

• 다음으로 활성 시트에서 마우스 오른쪽 버튼을 클릭한 후 '코드 보기(View Code)'를 선택합니다.

• 열린 빈 VBA 모듈에 아래 코드를 입력합니다.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler
Dim DV_Range As Range
Dim Row_1 As Long
Dim Col_1 As Long
Col_1 = Target.Column
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set DV_Range = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If DV_Range Is Nothing Then GoTo exitHandler
If Intersect(Target, DV_Range) Is Nothing Then
Else
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Select Case Target.Column
Case 4
If Target.Offset(0, 1).Value = "" Then
Row_1 = Target.Row
Else
Row_1 = Cells(Rows.Count, Col_1 + 1).End(xlUp).Row + 1
End If
Cells(Row_1, Col_1 + 1).Value = Target.Value
Target.ClearContents
End Select
End If
exitHandler:
Application.EnableEvents = True
End Sub

• 실행(Run) 아이콘을 클릭하거나 F5 키를 눌러 코드를 실행합니다.

• 매크로(Macros) 대화 상자가 나타나면 매크로 이름을 'VBA2'로 지정하고 실행을 클릭합니다.

• 이제 셀 D5의 드롭다운 목록에서 'USA'를 선택합니다.

• 그러면 'USA'가 같은 행의 인접 열로 자동 이동하고, 원래 셀(D5)은 비워집니다.

• 마지막으로 'Canada', 'Mexico', 'England'를 차례대로 선택하면, 선택된 값들이 동일한 열 안에서 개별 행으로 하나씩 정렬되는 것을 확인할 수 있습니다.

더 읽어보기: 필터가 적용된 Excel 데이터 유효성 검사 드롭다운 목록 (2가지 예제)

마무리

이번 튜토리얼에서는 데이터 유효성 검사 드롭다운 목록에서 다중 선택을 구현하는 세 가지 방법을 살펴봤습니다. 하나의 셀에 쉼표로 구분해 저장하는 방법, 인접 열로 가로 분산하는 방법, 그리고 개별 행으로 세로 정렬하는 방법까지 상황에 맞게 활용해 보세요. 배운 내용을 직접 연습하려면 글과 함께 제공되는 연습용 통합 문서를 다운로드해 활용하시기 바랍니다. 궁금한 점이 있다면 아래 댓글로 남겨주세요. 최대한 빠르게 답변드리겠습니다. 앞으로도 더욱 유용한 Microsoft Excel 팁으로 찾아뵙겠습니다.

함께 읽으면 좋은 글

• 데이터 유효성 검사 수식에서 IF 함수 활용하기 (6가지 방법)
• 색상과 함께 데이터 유효성 검사 사용하기 (4가지 방법)
• 다른 시트의 목록으로 데이터 유효성 검사 만들기 (6가지 방법)
• VBA로 명명된 범위를 활용한 데이터 유효성 검사 목록 사용법
• [해결됨] Excel에서 복사·붙여넣기 시 데이터 유효성 검사가 작동하지 않을 때