데이터 유효성 검사 드롭다운 목록은 다양한 Excel 관련 작업을 수행하는 데 매우 유용한 기능입니다. VBA 구현 Excel에서 모든 작업을 실행하는 가장 효과적이고 빠르고 안전한 방법입니다. 이 기사에서는 데이터 검증 드롭다운 목록의 7가지 애플리케이션을 보여줍니다. VBA 매크로를 사용하여 Excel에서 .
통합 문서 다운로드
여기에서 무료 연습 Excel 워크북을 다운로드할 수 있습니다.
Excel의 데이터 유효성 검사 드롭다운 목록에서 VBA를 사용하는 7가지 방법
이 섹션에서는 데이터 유효성 검사 드롭다운 목록의 7가지 응용 프로그램에 대해 알아봅니다. VBA를 사용하여 Excel에서 매크로.
1. VBA를 포함하여 Excel에서 데이터 유효성 검사 드롭다운 목록 만들기
VBA로 데이터 유효성 검사 드롭다운 목록을 만드는 방법 알아보기 , 아래 단계를 따르세요.
단계:
- 처음에 Alt + F11을 누릅니다. 키보드에서 또는 개발자 -> Visual Basic 탭으로 이동합니다. Visual Basic Editor 열기 .
- 그런 다음 팝업 코드 창의 메뉴 표시줄에서 삽입 -> 모듈을 클릭합니다. .
- 그 후 복사 다음 코드 및 붙여넣기 코드 창에 넣습니다.
Sub CreateDropDownList()
Range("B5").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="Grapes, Orange, Guava, Mango, Apple"
End Sub이제 코드를 실행할 준비가 되었습니다.
이 코드는 B5 셀에 드롭다운 목록을 생성합니다. . 드롭다운 목록에는 "포도, 오렌지, 구아바, 망고, 사과 값이 표시됩니다. ".
- 이제 F5 키를 누릅니다. 키보드 또는 메뉴 모음에서 실행 -> 하위/UserForm 실행을 선택합니다. . 작은 실행 아이콘을 클릭할 수도 있습니다. 하위 메뉴 표시줄에서 매크로를 실행합니다.
코드 실행 후 결과를 확인하려면 아래 이미지를 참조하세요.
위 이미지에서 볼 수 있듯이 B5셀에 드롭다운 목록이 생성된 것을 볼 수 있습니다. 값 "포도, 오렌지, 구아바, 망고, 사과 ".
자세히 알아보기: 데이터 유효성 검사를 위한 Excel 드롭다운 목록을 만드는 방법(8가지 방법)
2. Excel에서 VBA를 사용하여 명명된 범위별로 드롭다운 목록 생성
코드 안에 드롭다운 목록의 모든 값을 쓰고 싶지 않다면 모든 이름을 정의된 이름 안에 넣을 수 있습니다. 나중에 값 범위를 호출하는 데 사용합니다. 이것은 Excel에서 드롭다운 목록을 만드는 데 매우 편리한 방법입니다.
이 섹션에서는 명명 범위를 사용하여 주어진 목록에서 드롭다운 목록을 생성하는 방법을 배웁니다. VBA 사용 코드.
단계:
- 먼저, 범위 선택 드롭다운 목록의 값이 있는 위치(이 경우 범위는 B5:B9입니다. ).
- 둘째, 오른쪽 클릭 선택한 범위에서.
- 옵션 목록이 나타납니다. 여기에서 이름 정의...를 선택합니다.
- 그 후 새 이름 팝업 상자가 나타납니다. 이름에서 상자에 이름을 작성합니다. 당신이 좋아하는 (우리는 우리 세포를 과일 ).
- 나중에 확인을 클릭합니다. .
- 범위 이름을 성공적으로 지정했습니다. B5:B9 과일 (아래 그림 참조).
이제 이 VBA 코드에서 정의된 이름을 사용합니다. . 이를 수행하는 단계는 아래에 나와 있습니다.
- 이전과 같은 방식으로 Visual Basic Editor를 엽니다. 개발자 탭 및 삽입 모듈 코드 창에서
- 그런 다음 코드 창에서 복사 다음 코드를 입력하고 붙여넣기 그것.
Sub GenerateDropDownList()
Range("B12").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Fruits"
End Sub이제 코드를 실행할 준비가 되었습니다.
이 코드는 B12 셀에 드롭다운 목록을 만듭니다. 값 "포도, 오렌지, 구아바, 망고, 사과 "는 과일 이름에 정의되어 있습니다. .
- 나중에실행 매크로. 결과는 아래 이미지와 같습니다.
결과적으로 위 이미지에서 셀 B12에 생성된 드롭다운 목록이 있음을 알 수 있습니다. 값 "포도, 오렌지, 구아바, 망고, 사과 ".
자세히 알아보기: Excel에서 다중 선택으로 데이터 유효성 검사 드롭다운 목록 만들기
3. 매크로를 사용하여 주어진 목록에서 데이터 유효성 검사 드롭다운 상자 생성
명명된 범위 옵션이 마음에 들지 않는다면 이 섹션이 적합합니다. 여기에서는 범위에서 드롭다운 목록을 생성하는 방법을 배웁니다. 데이터세트에 존재합니다.
단계:
- 이전에 표시된 대로 Visual Basic Editor를 엽니다. 개발자 탭 및 삽입 모듈 코드 창에서
- 그런 다음 복사 다음 코드 및 붙여넣기 코드 창에 넣습니다.
Sub ProduceDropDownList()
With Range("B12").Validation
.Add xlValidateList, xlValidAlertStop, xlBetween, "=$B$5:$B$10"
.InCellDropdown = True
End With
End Sub이제 코드를 실행할 준비가 되었습니다.
이 코드는 셀 B12의 드롭다운 목록을 생성합니다. B5:B9 범위에 있는 값 .
- 이제 달려라 매크로를 실행하고 다음 이미지를 보고 출력을 확인하세요.
결과적으로 위 이미지에서 셀 B12에 생성된 드롭다운 목록이 있음을 알 수 있습니다. 값 "포도, 오렌지, 구아바, 망고, 사과 ”를 셀 B5에서 B9에 저장했습니다. 워크시트에서.
관련 콘텐츠: Excel VBA를 사용한 데이터 유효성 검사 목록의 기본값(매크로 및 UserForm)
유사한 수치:
- 필터가 있는 Excel 데이터 유효성 검사 드롭다운 목록(2가지 방법)
- 데이터 유효성 검사 영숫자만(맞춤 수식 사용)
- 다른 셀 값을 기반으로 한 Excel 데이터 유효성 검사
- Excel 데이터 유효성 검사에서 사용자 지정 VLOOKUP 수식을 사용하는 방법
4. Excel에서 여러 드롭다운 목록을 만들기 위한 VBA 구현
생성할 수 있습니다. 여러 드롭다운 목록 VBA 포함 매크로. Excel에서 수행하는 방법을 살펴보겠습니다.
단계:
- 처음에는 Visual Basic Editor를 엽니다. 개발자 탭 및 삽입 모듈 코드 창에서
- 둘째, 코드 창에서 복사 다음 코드를 입력하고 붙여넣기 그것.
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에서 B9 범위의 모든 셀에 드롭다운 목록을 생성합니다. .
- 셋째, 실행 매크로. 결과를 보려면 아래 gif를 보세요.
B5~B9 범위의 모든 셀 현재 드롭다운 목록을 보유하고 있습니다. .
자세히 알아보기: Excel에서 여러 기준에 대한 사용자 지정 데이터 유효성 검사 적용(예제 4개)
5. VBA를 적용하여 사용자 정의 함수로 드롭다운 목록 만들기
사용자 정의 함수(UDF)를 사용하여 드롭다운 목록을 생성할 수도 있습니다. 엑셀에서.
이를 얻는 단계는 아래에 나와 있습니다.
단계:
- 처음에 시트를 마우스 오른쪽 버튼으로 클릭 UDF를 구현하려는 위치 드롭다운 목록을 생성합니다.
- 그런 다음 코드 보기를 선택합니다. 나타난 목록에서. 아래와 같이 오른쪽 클릭했습니다. 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 키를 누릅니다. .
결과적으로 UDF에서 만든 드롭다운 목록이 표시됩니다. 셀 B11 값 "포도, 오렌지, 구아바, 망고, 사과 "는 B5:B9 범위에 저장됩니다. 함수 내부에 전달했습니다.
자세히 알아보기: 엑셀의 데이터 검증 수식에서 IF 문을 사용하는 방법(6가지 방법)
6. VBA를 사용하여 드롭다운 목록의 다른 시트에서 데이터 추출
다음 이미지를 보십시오. 목록이라는 시트에 데이터세트가 있습니다. .
여기서 할 일은 셀 B5에 드롭다운 목록을 생성하는 것입니다. 대상 이라는 시트의 (아래 그림에 표시). 그리고 해당 드롭다운 목록의 값은 B5:B9 범위의 값이 됩니다. 목록 시트.
VBA를 사용하여 이를 수행하는 방법에 대한 단계를 살펴보겠습니다. .
단계:
- 먼저 Visual Basic Editor를 엽니다. 개발자 탭 및 삽입 모듈 코드 창에서
- 그런 다음 복사 다음 코드 및 붙여넣기 코드 창에 넣습니다.
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이제 코드를 실행할 준비가 되었습니다.
- 나중에 실행 매크로를 실행하고 다음 이미지를 보고 출력을 확인하세요.
성공적인 코드 실행의 결과로 셀 B5에 생성된 드롭다운 목록이 있습니다. 대상 "포도, 오렌지, 구아바, 망고, 사과 값이 있는 워크시트 "는 B5:B9 범위에 저장됩니다. of the List spreadsheet.
자세히 알아보기: How to Use Data Validation List from Another Sheet (6 Methods)
7. Deleting Data Validation Drop Down List from Excel with VBA Macro
This section will show you how to delete a dropdown list from Excel. We will show you how to delete the existing dropdown list in Cell B5 ( shown in the image below) with the VBA 매크로.
The steps to execute that are given below.
단계:
- At first, open Visual Basic Editor 개발자 탭 및 삽입 모듈 코드 창에서
- Then, in the code window, copy 다음 코드 및 붙여넣기
Sub DeleteDropDownList()
Range("B5").Validation.Delete
End Sub이제 코드를 실행할 준비가 되었습니다.
- Later, Run the macro and look at the following image.
As you can see from the above image is that there is no dropdown list anymore in Cell B5 . Finally, we have learnt how to delete an existing dropdown list from a spreadsheet with VBA .
자세히 알아보기: Excel의 데이터 유효성 검사 목록에서 공백을 제거하는 방법(5가지 방법)
결론
To conclude, this article showed you 7 different applications of the data validation drop-down list VBA 매크로를 사용하여 Excel에서 . 이 글이 당신에게 많은 도움이 되었기를 바랍니다. 주제와 관련하여 언제든지 질문하십시오.
관련 기사
- [수정됨] Excel에서 복사 붙여넣기에 대한 데이터 유효성 검사가 작동하지 않음(솔루션 포함)
- Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법
- 배열에서 데이터 유효성 검사 목록을 만드는 Excel VBA
- 색상이 있는 Excel에서 데이터 유효성 검사를 사용하는 방법(4가지 방법)
- Excel의 한 셀에 여러 데이터 유효성 검사 적용(예제 3개)