엑셀 VBA로 드롭다운 목록에서 여러 값 선택하기 (중복 포함/제외 2가지 방법)

드롭다운 목록은 엑셀에서 다양한 작업을 수행할 때 매우 유용한 기능입니다. 그리고 엑셀에서 어떤 작업이든 가장 효과적이고 빠르며 안전하게 실행하는 방법은 바로 VBA를 활용하는 것입니다. 이 글에서는 VBA 매크로를 사용해 엑셀의 드롭다운 목록에서 값을 선택(복수 선택)하는 효과적인 방법 2가지를 소개하겠습니다.

실습용 워크북 다운로드

아래에서 무료 실습용 엑셀 워크북을 내려받아 직접 따라 해볼 수 있습니다.

일반 목록으로 드롭다운 목록 만들기

코드 작성에 들어가기 전에, 먼저 일반 목록 데이터를 활용해 드롭다운 목록을 만드는 아주 간단한 방법부터 알아보겠습니다. 이렇게 만든 드롭다운 목록은 이후 이 글의 예제로 계속 활용됩니다.

아래는 엑셀 워크시트에 있는 일반 목록입니다. 목록에는 중복된 값(예: B7 셀과 B9 셀의 Apple)이 포함되어 있습니다.

이 일반 목록의 값들을 이용해(Grapes, Orange, Apple, Mango, Apple 등) 드롭다운 목록을 만드는 방법을 살펴보겠습니다.

순서:

• 먼저 드롭다운 목록을 표시할 셀(예제에서는 D4 셀)을 클릭합니다.
• 상단 리본 메뉴에서 데이터 탭을 클릭합니다.
• 데이터 도구 그룹에서 데이터 유효성 검사를 선택합니다.

• 데이터 유효성 검사 팝업 창이 나타나면 다음과 같이 설정합니다.
  • 제한 대상 항목에서 목록을 선택합니다.
  • 원본 항목에는 드롭다운 목록에 넣을 값이 있는 범위(예제에서는 B5:B9)를 마우스로 끌어 지정합니다.
• 마지막으로 확인을 클릭합니다.

아래 이미지를 참고하세요.

D4 셀에 드롭다운 목록이 생성되었으며, 일반 목록(B5:B9 범위)에서 가져온 값들(Grapes, Orange, Apple, Mango, Apple)이 포함되어 있습니다.

VBA로 드롭다운 목록에서 값 선택하기: 2가지 방법

이번 섹션에서는 VBA를 활용해 드롭다운 목록에서 중복 값을 포함한 복수 선택과 중복 없이 복수 선택을 구현하는 방법을 배워보겠습니다.

방법 1. 드롭다운 목록에서 여러 값 선택하기 (중복 값 허용)

데이터에 중복 값이 있고, 드롭다운 목록에서 같은 값이라도 모두 선택되도록 하고 싶다면 아래 단계를 따라 하세요.

순서:

• 먼저 키보드에서 Alt + F11을 누르거나 개발 도구 → Visual Basic 탭으로 이동해 VBA 편집기(Visual Basic Editor)를 엽니다.

• 다음으로 해당 시트 이름을 마우스 오른쪽 버튼으로 클릭하고 나타난 메뉴에서 코드 보기(View Code)를 선택합니다.

• 그런 다음 아래 코드를 복사해서 코드 창에 붙여넣기 합니다.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ValueA As String
Dim ValueB 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
        ValueB = Target.Value
        Application.Undo
        ValueA = Target.Value
        If ValueA = "" Then
            Target.Value = ValueB
        Else
            Target.Value = ValueA & ", " & ValueB
        End If
    End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

• 이 코드는 실행하지 말고 저장만 하세요.
• 이제 해당 워크시트로 돌아가 D4 셀의 드롭다운 목록을 클릭해 보면, 드롭다운에서 여러 개의 값을 선택할 수 있게 된 것을 확인할 수 있습니다(아래 GIF 참고).

위 GIF에서 확인할 수 있듯이, 이 VBA 코드를 사용하면 특정 값을 여러 번 반복해서 선택할 수도 있습니다. 즉, 이 매크로는 드롭다운 목록에서 모든 종류의 값을 그대로 누적 선택하도록 동작합니다.

VBA 코드 설명

Dim ValueA As String
Dim ValueB As String

변수 이름을 선언하는 부분입니다.

On Error GoTo Exitsub

오류가 발생하면 Exitsub 레이블로 이동합니다.

If Target.Address = "$D$4" Then
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub

대상 셀을 데이터 유효성 검사가 적용된 D4 셀로 지정합니다. 데이터 유효성 검사가 있는 셀이 없으면 Exitsub 레이블로 이동합니다.

Else: If Target.Value = "" Then GoTo Exitsub Else

대상 셀이 비어 있으면 Exitsub 레이블로 이동하고, 그렇지 않으면 다음 줄들을 실행합니다.

Application.EnableEvents = False

응용 프로그램 이벤트를 꺼서 Worksheet_Change 매크로가 반복 실행되는 것을 방지합니다. 이벤트를 끄지 않으면 무한 루프에 빠질 수 있습니다.

ValueB = Target.Value

변경된 셀의 새 값을 ValueB에 저장합니다.

Application.Undo

변경된 셀의 입력을 되돌립니다(Undo).

ValueA = Target.Value

변경을 되돌렸기 때문에, 변경 전의 기존 값을 ValueA에 저장할 수 있습니다.

If ValueA = "" Then
    Target.Value = ValueB

기존 값이 비어 있다면, 새 값만 대상 셀에 저장합니다.

Else
    Target.Value = ValueA & ", " & ValueB
  End If
 End If
End If

기존 값이 있다면 기존 값과 새 값을 쉼표(,)로 연결해 함께 저장합니다. 이후 모든 If 문을 닫습니다.

Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True

응용 프로그램 이벤트를 다시 켭니다.

더 읽어보기: 엑셀에서 다중 선택 가능한 드롭다운 목록 만드는 방법

함께 보면 좋은 글:

• 다른 시트 데이터로 드롭다운 목록 만들기 (2가지 방법)
• 검색 가능한 드롭다운 목록 만들기 (2가지 방법)
• 엑셀 드롭다운 목록이 작동하지 않을 때 (8가지 문제와 해결책)
• 표(Table)로 엑셀 드롭다운 목록 만들기 (5가지 예제)
• 엑셀 드롭다운 목록 자동 업데이트하기 (3가지 방법)

방법 2. 드롭다운 목록에서 여러 값 선택하기 (중복 값 제외)

데이터에 중복 값이 있지만, 드롭다운 목록에서 중복된 값은 제외하고 나머지 값만 모두 선택되도록 하고 싶다면 아래 단계를 따라 하세요.

순서:

• 앞서와 같이 개발 도구 탭에서 VBA 편집기를 엽니다.
• 해당 워크시트를 오른쪽 클릭한 후 코드 보기(View Code) 옵션으로 코드 창으로 이동합니다.
• 아래 코드를 복사해서 지정된 워크시트의 코드 창에 붙여넣기 합니다.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ValueA As String
Dim ValueB 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
    ValueB = Target.Value
    Application.Undo
    ValueA = Target.Value
      If ValueA = "" Then
        Target.Value = ValueB
      Else
        If InStr(1, ValueA, ValueB) = 0 Then
            Target.Value = ValueA & ", " & ValueB
      Else:
        Target.Value = ValueA
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

• 이 코드 역시 실행하지 말고 저장만 하세요.
• 이제 해당 워크시트로 돌아가 D4 셀의 드롭다운 목록을 클릭해 보면, 드롭다운에서 여러 개의 값을 선택할 수 있습니다(아래 GIF 참고).

위 GIF에서 확인할 수 있듯이, 이 VBA 코드로는 같은 값을 여러 번 반복 선택할 수 없습니다. 즉, 이 매크로는 드롭다운 목록에서 중복 없이 고유한 값만 선택되도록 동작합니다.

VBA 코드 설명

Dim ValueA As String
Dim ValueB As String

변수 이름을 선언하는 부분입니다.

Application.EnableEvents = True

응용 프로그램 이벤트를 켭니다.

On Error GoTo Exitsub

오류가 발생하면 Exitsub 레이블로 이동합니다.

If Target.Address = "$D$4" Then
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub

대상 셀을 데이터 유효성 검사가 적용된 D4 셀로 지정합니다. 데이터 유효성 검사가 있는 셀이 없으면 Exitsub 레이블로 이동합니다.

Else: If Target.Value = "" Then GoTo Exitsub Else

대상 셀이 비어 있으면 Exitsub 레이블로 이동하고, 그렇지 않으면 다음 줄들을 실행합니다.

Application.EnableEvents = False

응용 프로그램 이벤트를 꺼서 Worksheet_Change 매크로가 반복 실행되며 무한 루프에 빠지는 것을 방지합니다.

ValueB = Target.Value

변경된 셀의 새 값을 ValueB에 저장합니다.

Application.Undo

변경된 셀의 입력을 되돌립니다(Undo).

ValueA = Target.Value

변경을 되돌렸기 때문에, 변경 전의 기존 값을 ValueA에 저장할 수 있습니다.

If ValueA = "" Then
    Target.Value = ValueB

기존 값이 비어 있다면, 새 값만 대상 셀에 저장합니다.

Else
    If InStr(1, ValueA, ValueB) = 0 Then
        Target.Value = ValueA & ", " & ValueB

InStr 함수는 문자열 안에서 특정 부분 문자열이 처음 나타나는 위치를 반환합니다. 결과가 0이면(즉, 새 값이 기존 값에 없으면) 기존 값과 새 값을 쉼표(,)로 연결해 함께 저장합니다.

Else: Target.Value = ValueA
    End If
   End If
  End If
 End If

이미 값이 존재한다면 기존 값만 유지합니다(중복 추가 방지). 이후 모든 If 문을 닫습니다.

Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True

응용 프로그램 이벤트를 다시 켭니다.

더 읽어보기: 선택값에 따라 자동으로 바뀌는 엑셀 드롭다운 목록 만들기

마무리

지금까지 VBA 매크로를 사용해 엑셀의 드롭다운 목록에서 값을 선택(복수 선택)하는 효과적인 방법 2가지를 살펴보았습니다. 중복 값을 허용하는 방법과 중복을 제외하는 방법, 각각의 상황에 맞게 활용해 보시기 바랍니다. 이 글이 도움이 되었기를 바라며, 주제와 관련해 궁금한 점이 있다면 언제든 질문해 주세요.

관련 글

• 수식 기반으로 드롭다운 목록 만들기 (4가지 방법)
• 엑셀 조건부 드롭다운 목록 (만들기, 정렬, 활용)
• IF 문으로 엑셀 드롭다운 목록 만드는 방법
• 셀 값과 드롭다운 목록 연결하기 (5가지 방법)
• 엑셀 드롭다운 목록 삭제하는 방법