데이터 유효성 검사 드롭다운 목록은 엑셀에서 다양한 작업을 수행할 때 매우 유용한 기능입니다. 특히 VBA를 활용하면 엑셀의 어떤 작업이든 가장 빠르고 안정적으로 자동화할 수 있습니다. 이 글에서는 VBA 매크로를 사용해 엑셀에서 데이터 유효성 검사 드롭다운 목록을 만들고 활용하는 7가지 방법을 단계별로 소개합니다.
실습 파일 다운로드
아래에서 무료 실습용 엑셀 통합 문서를 내려받아 직접 따라 해볼 수 있습니다.
VBA로 구현하는 데이터 유효성 검사 드롭다운 목록 7가지 방법
이 섹션에서는 VBA 매크로를 활용해 엑셀에서 데이터 유효성 검사 드롭다운 목록을 적용하는 7가지 방법을 하나씩 살펴보겠습니다.
1. VBA로 데이터 유효성 검사 드롭다운 목록 만들기
VBA로 데이터 유효성 검사 드롭다운 목록을 생성하는 기본 방법부터 알아보겠습니다.
단계:
- 먼저 키보드에서 Alt + F11을 누르거나 개발 도구 → Visual Basic 탭으로 이동해 VBA 편집기(Visual Basic Editor)를 엽니다.
- 코드 창 상단 메뉴에서 삽입 → 모듈(Insert → Module)을 클릭합니다.
- 아래 코드를 복사해서 코드 창에 붙여넣습니다.
Sub CreateDropDownList()
Range("B5").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="Grapes, Orange, Guava, Mango, Apple"
End Sub이제 코드를 실행할 준비가 되었습니다.
이 코드는 B5 셀에 드롭다운 목록을 만들며, 목록에는 “Grapes, Orange, Guava, Mango, Apple” 값이 포함됩니다.
- F5 키를 누르거나 메뉴에서 실행 → Sub/UserForm 실행(Run → Run Sub/UserForm)을 선택해 매크로를 실행합니다. 하위 메뉴 모음의 작은 실행 아이콘을 클릭해도 됩니다.
코드 실행 후 결과는 아래 이미지와 같습니다.
위 이미지에서 볼 수 있듯이 B5 셀에 “Grapes, Orange, Guava, Mango, Apple” 값을 가진 드롭다운 목록이 생성되었습니다.
더 읽어보기: 엑셀에서 데이터 유효성 검사용 드롭다운 목록 만드는 방법(8가지)
2. 명명된 범위(Named Range)를 활용한 드롭다운 목록 만들기
드롭다운 목록의 모든 값을 코드에 일일이 작성하고 싶지 않다면, 값들을 정의된 이름(Defined Name)에 저장한 뒤 해당 이름을 호출하는 방식을 사용할 수 있습니다. 엑셀에서 드롭다운 목록을 만들 때 매우 편리한 방법입니다.
이 섹션에서는 명명된 범위(Named Range)와 VBA 코드를 사용해 지정된 목록으로 드롭다운 목록을 생성하는 방법을 배웁니다.
단계:
- 먼저 드롭다운 목록 값이 들어 있는 범위를 선택합니다(예제에서는 B5:B9).
- 선택한 범위에서 마우스 오른쪽 버튼을 클릭합니다.
- 나타나는 옵션 목록에서 이름 정의(Define Name)…를 선택합니다.
- 새 이름(New Name) 팝업 창이 열리면 이름(Name) 입력란에 원하는 이름을 입력합니다(예제에서는 Fruits로 지정했습니다).
- 그다음 확인(OK)을 클릭합니다.
- 이렇게 하면 범위 B5:B9가 Fruits라는 이름으로 성공적으로 지정됩니다(아래 그림 참조).
이제 이 정의된 이름을 VBA 코드에서 사용해 보겠습니다. 절차는 아래와 같습니다.
- 앞서와 같이 개발 도구 탭에서 VBA 편집기를 열고 코드 창에 모듈을 삽입합니다.
- 그런 다음 코드 창에 아래 코드를 복사해 붙여넣습니다.
Sub GenerateDropDownList()
Range("B12").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Fruits"
End Sub이제 코드를 실행할 준비가 되었습니다.
이 코드는 B12 셀에 드롭다운 목록을 생성하며, 이름 Fruits에 정의된 “Grapes, Orange, Guava, Mango, Apple” 값이 표시됩니다.
- 매크로를 실행하면 결과는 아래 이미지와 같습니다.
위 이미지처럼 B12 셀에 “Grapes, Orange, Guava, Mango, Apple” 값을 가진 드롭다운 목록이 생성되었습니다.
더 읽어보기: 엑셀에서 다중 선택 가능한 데이터 유효성 검사 드롭다운 목록 만들기
3. 매크로로 특정 범위의 목록에서 드롭다운 상자 만들기
명명된 범위 방식이 마음에 들지 않는다면 이 섹션이 딱 맞습니다. 여기서는 워크시트에 존재하는 데이터 범위에서 직접 드롭다운 목록을 생성하는 방법을 배웁니다.
단계:
- 앞서와 동일하게 개발 도구 탭에서 VBA 편집기를 열고 코드 창에 모듈을 삽입합니다.
- 그다음 아래 코드를 복사해서 코드 창에 붙여넣습니다.
Sub ProduceDropDownList()
With Range("B12").Validation
.Add xlValidateList, xlValidAlertStop, xlBetween, "=$B$5:$B$10"
.InCellDropdown = True
End With
End Sub이제 코드를 실행할 준비가 되었습니다.
이 코드는 B5:B9 범위에 있는 값들로 B12 셀에 드롭다운 목록을 생성합니다.
- 매크로를 실행한 뒤 아래 이미지에서 출력 결과를 확인하세요.
실행 결과, 워크시트의 B5~B9 셀에 저장된 “Grapes, Orange, Guava, Mango, Apple” 값으로 B12 셀에 드롭다운 목록이 생성된 것을 확인할 수 있습니다.
관련 콘텐츠: Excel VBA로 데이터 유효성 검사 목록의 기본값 설정하기(매크로 및 UserForm)
함께 보면 좋은 글:
- 필터가 적용된 엑셀 데이터 유효성 검사 드롭다운 목록(2가지 방법)
- 사용자 지정 수식으로 영숫자만 입력 허용하기(데이터 유효성 검사)
- 다른 셀 값을 기준으로 하는 엑셀 데이터 유효성 검사
- 엑셀 데이터 유효성 검사에서 사용자 지정 VLOOKUP 수식 활용법
4. VBA로 여러 개의 드롭다운 목록 한 번에 만들기
VBA 매크로를 사용하면 여러 셀에 드롭다운 목록을 동시에 생성할 수도 있습니다. 엑셀에서 어떻게 하는지 살펴보겠습니다.
단계:
- 먼저 개발 도구 탭에서 VBA 편집기를 열고 코드 창에 모듈을 삽입합니다.
- 그다음 코드 창에 아래 코드를 복사해 붙여넣습니다.
Sub MultipleDropDownList(iTarget As Range, iSource As Range)
'to delete and add validation in the target range
With iTarget.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & iSource.Address
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
Sub DropDownRange()
MultipleDropDownList Sheet7.Range("B5:B10"), Sheet7.Range("A1:A3")
End Sub이제 코드를 실행할 준비가 되었습니다.
이 코드는 B5부터 B10까지의 모든 셀에 드롭다운 목록을 생성합니다.
- 매크로를 실행하면 결과는 아래 GIF에서 확인할 수 있습니다.
B5~B10 범위의 모든 셀에 각각 드롭다운 목록이 생성되었습니다.
더 읽어보기: 엑셀에서 여러 조건에 맞는 사용자 지정 데이터 유효성 검사 적용하기(4가지 예제)
5. 사용자 정의 함수(UDF)로 드롭다운 목록 만들기
엑셀에서는 사용자 정의 함수(UDF)를 활용해서도 드롭다운 목록을 만들 수 있습니다.
방법은 아래 단계를 따르세요.
단계:
- 먼저 UDF를 적용해 드롭다운 목록을 만들 시트에서 마우스 오른쪽 버튼을 클릭합니다.
- 나타나는 목록에서 코드 보기(View Code)를 선택합니다. 아래 예제에서는 데이터가 저장된 UDF 시트를 오른쪽 클릭한 뒤 옵션에서 코드 보기를 선택했습니다.
- 자동으로 열린 코드 창에 아래 코드를 복사해 붙여넣습니다.
Public Function DropDownUDF(iSource As Range) As Variant
'to delete and add validation in the specified range
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & iSource.Address
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
'this will return the first value
'this reset the values when formula in sheet are refreshed
DropDownUDF = VBA.Val(iSource(1))
End Function- 이 코드는 실행하지 말고 저장만 하세요.
- 그다음 작업 중인 워크시트로 돌아갑니다.
- 드롭다운 목록을 만들고 싶은 아무 셀이나 선택합니다(예제에서는 B11 셀).
- 해당 셀에 일반 함수를 입력하듯 새로 만든 함수 DropDownUDF를 작성합니다. 즉, 먼저 등호(=)를 입력한 뒤 함수 이름 DropDownUDF를 쓰고, 괄호 안에 셀 참조(B5:B9)를 전달합니다.
B11 셀에 입력할 수식은 다음과 같습니다.
=DropDownUDF(B5:B9)
- Enter 키를 누릅니다.
결과적으로 함수에 전달한 B5:B9 범위에 저장된 “Grapes, Orange, Guava, Mango, Apple” 값을 가진 UDF 기반 드롭다운 목록이 B11 셀에 생성됩니다.
더 읽어보기: 엑셀 데이터 유효성 검사 수식에서 IF문 활용법(6가지)
6. VBA로 다른 시트의 데이터를 드롭다운 목록으로 가져오기
아래 이미지를 보면 List라는 이름의 시트에 데이터 집합이 있습니다.
여기서 할 작업은 Target 시트(아래 그림 참조)의 B5 셀에 드롭다운 목록을 만들고, 그 목록의 값으로 List 시트 B5:B9 범위의 값을 사용하는 것입니다.
VBA로 이를 구현하는 단계를 살펴보겠습니다.
단계:
- 먼저 개발 도구 탭에서 VBA 편집기를 열고 코드 창에 모듈을 삽입합니다.
- 그다음 아래 코드를 복사해서 코드 창에 붙여넣습니다.
Private Sub DropDownFromSheet()
'to store the dropdown list in cell B5
'you can replace "B5" with any other cell
With Range("B5").Validation
.Delete
'to extract data from "List" sheet and "B5:B9" range
'you can replace "=List!B5:B9" with your sheet name and range
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=List!B5:B9"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub이제 코드를 실행할 준비가 되었습니다.
- 매크로를 실행한 뒤 아래 이미지에서 출력 결과를 확인하세요.
코드가 성공적으로 실행되면 List 시트의 B5:B9 범위에 저장된 “Grapes, Orange, Guava, Mango, Apple” 값을 가진 드롭다운 목록이 Target 시트의 B5 셀에 생성됩니다.
더 읽어보기: 다른 시트의 데이터로 데이터 유효성 검사 목록 만드는 방법(6가지)
7. VBA 매크로로 데이터 유효성 검사 드롭다운 목록 삭제하기
이 섹션에서는 엑셀에서 드롭다운 목록을 삭제하는 방법을 알려드립니다. VBA 매크로로 B5 셀에 있는 기존 드롭다운 목록(아래 이미지 참조)을 제거하는 과정을 살펴보겠습니다.
실행 절차는 아래와 같습니다.
단계:
- 먼저 개발 도구 탭에서 VBA 편집기를 열고 코드 창에 모듈을 삽입합니다.
- 그런 다음 코드 창에 아래 코드를 복사해 붙여넣습니다.
Sub DeleteDropDownList()
Range("B5").Validation.Delete
End Sub이제 코드를 실행할 준비가 되었습니다.
- 매크로를 실행한 뒤 아래 이미지를 확인하세요.
위 이미지에서 볼 수 있듯이 B5 셀에는 더 이상 드롭다운 목록이 남아 있지 않습니다. 이것으로 VBA를 사용해 스프레드시트에서 기존 드롭다운 목록을 삭제하는 방법까지 모두 배웠습니다.
더 읽어보기: 엑셀 데이터 유효성 검사 목록에서 빈 항목 제거하는 방법(5가지)
마치며
지금까지 VBA 매크로를 활용해 엑셀에서 데이터 유효성 검사 드롭다운 목록을 만들고 활용하는 7가지 방법을 살펴보았습니다. 이 글이 실무에 큰 도움이 되기를 바랍니다. 주제에 대해 궁금한 점이 있다면 언제든지 질문해 주세요.
관련 글
- [해결됨] 엑셀에서 복사·붙여넣기 시 데이터 유효성 검사가 작동하지 않는 문제(솔루션 포함)
- 엑셀에서 VBA로 명명된 범위를 데이터 유효성 검사 목록에 사용하는 방법
- 배열(Array)로 데이터 유효성 검사 목록을 만드는 엑셀 VBA
- 색상과 함께 엑셀 데이터 유효성 검사 활용하기(4가지 방법)
- 엑셀에서 한 셀에 여러 데이터 유효성 검사 적용하기(3가지 예제)