상황에 따라 드롭다운 목록에서 여러 값을 한꺼번에 선택해야 할 때가 있습니다. 이 튜토리얼에서는 드롭다운 목록에서 다중 선택을 구현하는 방법을 소개합니다. 이번 강좌는 Excel 2019를 기준으로 작성되었지만, 사용 중인 버전이 있다면 어떤 버전이든 무방합니다.
본격적으로 시작하기 전에, 예제의 기반이 되는 데이터셋부터 살펴보겠습니다.
여기에는 여러 문구류 항목이 있습니다. 이 항목들을 활용해 드롭다운 목록을 만들고, 그 안에서 여러 항목을 선택해 보겠습니다.
설명을 쉽게 이해할 수 있도록 간단한 데이터셋을 사용했으며, 실무에서는 훨씬 더 크고 복잡한 데이터를 다루게 될 수 있다는 점을 참고해 주세요.
실습용 워크북
아래 링크에서 실습용 워크북을 다운로드할 수 있습니다.
드롭다운 목록에서 다중 선택하기
먼저 문구류 항목을 바탕으로 드롭다운 목록을 만들어야 합니다. 빠르게 만들어 보겠습니다. 드롭다운 목록 생성 방법에 대한 별도의 글도 함께 참고하시면 좋습니다.
데이터 유효성 검사 대화 상자에서 목록(LIST) 데이터 형식을 선택하고, 항목이 있는 셀 범위를 입력합니다.
B4:B11은 문구류 항목이 들어 있는 범위입니다. 이제 드롭다운 목록이 생성된 것을 확인할 수 있습니다.
더 읽어보기: Excel 선택에 따라 변하는 드롭다운 목록 만들기
1. 여러 항목 선택하기 (중복 선택 허용)
일반적인 드롭다운 목록에서는 하나의 항목만 선택됩니다. 아래 이미지처럼 목록에서 펜을 선택했다고 가정해 보겠습니다.
이제 다른 항목, 예를 들어 연필을 선택하면
기존 값이 대체되어 연필만 남게 됩니다.
여러 항목을 선택하려면 VBA 코드를 사용해야 합니다. Microsoft Visual Basic for Applications 창을 엽니다(ALT + F11 키를 누르면 열립니다).
그런 다음 드롭다운 목록에서 여러 항목을 선택하고 싶은 워크시트의 이름이나 번호를 더블클릭합니다. 해당 시트의 코드 창이 열립니다.
여기서는 드롭다운 목록이 있는 Sheet2의 코드 창입니다.
코드 창이 열리면 다음 코드를 입력합니다.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
On Error GoTo Exitsub
If Target.Address = "$D$4" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
Target.Value = Oldvalue & ", " & Newvalue
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
코드를 저장한 후 드롭다운 목록에서 값을 선택해 보세요.
연필을 선택한 상태에서 다른 항목인 공책을 선택하면, 아래 이미지처럼 두 항목이 모두 표시됩니다.
이 코드는 동일한 항목을 반복해서 선택하는 것도 허용합니다. 예를 들어 연필을 다시 선택하면,
선택 상자에 해당 항목이 또다시 나타납니다.
코드 설명
Oldvalue와 Newvalue라는 두 개의 문자열 변수를 선언했습니다.
드롭다운 목록을 D4 셀에 만들었기 때문에 대상 주소는 D4입니다. 또한 Target.SpecialCells를 사용해 해당 셀에 데이터 유효성 검사가 실제로 적용되어 있는지 다시 한 번 확인합니다.
값이 선택되면 이벤트를 비활성화하여(Application.EnableEvents = False) 변경 사항이 이벤트를 다시 트리거하지 않도록 하고, 선택한 항목을 Newvalue에 저장합니다.
변경을 실행 취소한 후 그 값을 Oldvalue에 담습니다. 그다음 Oldvalue가 비어 있는지 확인합니다. 비어 있다면(즉, 첫 번째 선택이라는 의미) Newvalue를 그대로 반환하고, 그렇지 않으면 Oldvalue와 Newvalue를 쉼표로 연결합니다.
마지막으로 종료하기 전에 이벤트를 다시 활성화하여 이후에도 변경이 가능하도록 설정합니다.
더 읽어보기: Excel VBA로 다중 종속 드롭다운 목록 만들기
2. 드롭다운 목록에서 여러 항목 선택하기 (중복 없이 고유 값만)
앞선 섹션에서는 중복 선택이 허용되는 다중 선택 방법을 살펴봤습니다. 중복 선택을 원하지 않는다면 이 섹션을 따라 해보세요.
편의를 위해 이번 데모에서는 별도의 시트를 사용했습니다. 이번에는 Sheet3입니다. 이 시트의 코드 창에 다음 코드를 작성합니다.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$D$4" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & ", " & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
앞선 코드와 차이점이 보이시나요? 자세히 살펴보면 미세한 차이를 발견할 수 있습니다.
여기서는 INSTR이라는 VBA 함수를 사용했습니다. INSTR 함수는 문자열 안에서 특정 부분 문자열이 처음 나타나는 위치를 반환합니다. 자세한 내용은 INSTR 관련 글을 참고하세요.
InStr(1, Oldvalue, Newvalue) = 0이라는 논리 연산을 통해 해당 값이 이미 존재하는지 확인합니다. 이 논리식이 TRUE(이전에 없던 값)를 반환하면 항목 선택을 허용하고 기존 값과 연결합니다.
코드를 저장한 후 이미 선택된 항목을 다시 선택해 보세요.
이미 연필이 선택된 상태에서 다시 선택하려고 하면 선택되지 않습니다. 중복 값을 허용하지 않기 때문입니다.
더 읽어보기: Excel에서 다른 시트의 데이터로 드롭다운 목록 만드는 방법
3. 줄바꿈으로 항목 구분하기
지금까지는 선택된 항목들이 쉼표로 구분되었습니다. 이번 섹션에서는 선택한 항목들을 줄바꿈으로 정렬해 보겠습니다.
간단하게 하기 위해 D4 셀과 몇 개의 셀을 병합합니다. 병합하려는 셀들을 선택한 후 홈 탭의 맞춤 섹션에서 가운데 맞춤 병합을 클릭합니다.
셀의 높이가 늘어난 것을 확인할 수 있습니다.
이제 항목을 줄바꿈으로 구분하는 코드를 살펴보겠습니다. 다음 코드를 사용합니다.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$D$4" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & vbNewLine & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
이전 코드와 유일한 차이점은 이번에는 OldValue와 NewValue 사이에 vbNewLine을 사용했다는 점입니다.
vbNewLine은 항목 사이에 새 줄을 만들어 줍니다.
이제 항목을 선택해 봅니다.
위 이미지처럼 펜 항목을 선택했습니다. 이제 다른 항목을 선택합니다.
두 항목이 서로 다른 줄에 표시되는 것을 확인할 수 있습니다.
두 값이 각각 다른 줄에 배치되어 있습니다. 값을 추가로 선택하면 그 값 역시 새 줄에 추가되며, 모든 값이 각자 별도의 줄에 정렬됩니다.
참고로 다른 구분 기호를 사용하고 싶다면 vbNewLine 대신 큰따옴표 안에 원하는 구분 기호를 넣으면 됩니다.
마치며
오늘은 여기까지입니다. 드롭다운 목록에서 다중 선택을 구현하는 여러 가지 방법을 살펴봤습니다. 도움이 되었기를 바랍니다. 이해하기 어려운 부분이 있다면 언제든 댓글로 문의해 주시고, 소개하지 못한 다른 방법이 있다면 알려주세요.
함께 보면 좋은 글
- Excel에서 여러 열로 드롭다운 목록 만드는 방법(3가지)
- Excel에서 셀 값과 드롭다운 목록 연결하기(5가지 방법)
- Excel 조건부 드롭다운 목록(생성, 정렬 및 활용)
- Excel 동적 종속 드롭다운 목록 만들기
- IF 함수로 Excel 드롭다운 목록 만드는 방법
- Excel에서 VLOOKUP과 드롭다운 목록 함께 활용하기