Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법

데이터 유효성 검사를 위해 명명된 범위를 사용하는 가장 쉬운 방법을 찾고 있다면 Excel VBA의 목록 , 그러면 이 기사가 유용하다는 것을 알게 될 것입니다. 명명된 범위는 드롭다운 목록을 쉽게 만들기 위해 데이터 유효성 검사 공식에서 사용하는 데 유용하며 이 작업은 일부 VBA의 도움으로 매우 쉽게 만들 수 있습니다. 코드.
따라서 데이터 유효성 검사 목록에서 명명된 범위를 사용하는 방법을 살펴보기 위해 주요 기사를 시작하겠습니다.

통합 문서 다운로드

VBA를 사용하여 Excel에서 데이터 유효성 검사 목록에 명명된 범위를 사용하는 4가지 방법

여기에 일부 제품의 기록과 해당 판매원의 목록이 포함된 다음 데이터세트가 있습니다. 이 데이터 세트를 사용하여 다양한 VBA 데이터 유효성 검사 목록에서 명명된 범위를 사용하는 코드입니다.

Microsoft Excel 365를 사용했습니다. 여기에서 버전을 확인하고 편의에 따라 다른 버전을 사용할 수 있습니다.

방법-1 :드롭다운 목록을 만들기 위한 데이터 유효성 검사에서 명명된 범위 사용

여기에서는 과일 과일 열 VBA 사용 D6 셀에 드롭다운 목록을 생성할 코드 .

Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법

01단계 :
➤ 개발자 로 이동 탭>> 비주얼 베이직 옵션.

Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법

그런 다음 Visual Basic Editor 열 것입니다.
➤ 삽입 으로 이동 탭>> 모듈 옵션.

Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법

그 후 모듈 생성됩니다.

Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법

02단계 :
➤ 다음 코드 작성

Sub Datavalidation1()

Range("D6").Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Formula1:="=Fruits"

End Sub

여기 검증 D6 셀에 추가됩니다. , xlValidateList 드롭다운 목록을 만들기 위한 것이며 수식은 “=Fruits” 범위의 이름으로 사용됩니다. .

Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법

➤ F5 키를 누릅니다. 나중에 D6 셀의 드롭다운 기호를 클릭합니다. .
그런 다음 과일 목록을 가져오고 체리와 같은 목록에서 하나의 항목을 선택합니다. .

Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법

마지막으로 D6 셀에서 선택한 항목을 가져옵니다. .

Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법

자세히 보기:Excel의 표에서 데이터 유효성 검사 목록을 만드는 방법(3가지 방법)

방법-2 :VBA 코드로 명명된 범위 및 데이터 유효성 검사 목록 추가

여기서는 이름이 지정된 범위를 수동으로 생성하지 않고 단순한 VBA 코드는 명명된 범위를 형성하고 이를 사용하여 마지막으로 셀 D6에 드롭다운 목록을 얻습니다. .

Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법

단계 :
➤ Step-01 따르기 방법-1의 .
➤ 다음 코드 작성

Sub Datavalidation2()

ActiveWorkbook.Names.Add Name:="Fruit", _
RefersTo:=ThisWorkbook.Worksheets("Add").Range("B4:B10")
Range("D6").Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Formula1:="=Fruit"

End Sub

먼저 과일 이라는 이름을 추가합니다. “B4:B10” 범위로 워크시트의 추가 .
그런 다음 검증 을 추가합니다. D6 셀에 , xlValidateList 드롭다운 목록을 만들기 위한 것이며 수식은 “=Fruit” 범위의 이름으로 사용됩니다. .

Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법

➤ F5 키를 누릅니다. , 그런 다음 워크시트로 이동하여 D6 셀의 드롭다운 기호를 클릭합니다. .
나중에 과일 목록이 표시되고 블루베리와 같은 목록에서 하나의 항목을 선택합니다. .

Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법

그래서 우리는 원하는 품목 블루베리 를 얻었습니다. 목록에서 이 외에도 과일에 대해 생성된 명명된 범위를 볼 수 있습니다.

Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법

관련 콘텐츠:Excel에서 다중 선택으로 데이터 유효성 검사 드롭다운 목록 만들기

유사한 수치:

Excel의 자동 완성 데이터 유효성 검사 드롭다운 목록(2가지 방법)
필터가 있는 Excel 데이터 유효성 검사 드롭다운 목록(예제 2개)
Excel VBA(매크로 및 UserForm)를 사용한 데이터 유효성 검사 목록의 기본값
Excel에서 여러 기준에 대한 사용자 지정 데이터 유효성 검사 적용(예제 4개)
Excel 데이터 유효성 검사 영숫자만(사용자 지정 수식 사용)

방법-3 :Excel VBA를 사용하여 명명된 범위로 데이터 유효성 검사 목록 업데이트

D6 셀에 다음 드롭다운 목록이 있다고 가정합니다. , 고정 데이터세트에 대해 잘 작동합니다.

Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법

하지만 야채 상추를 추가하면 그러면 드롭다운 목록에 나타나지 않으므로 이 경우 드롭다운 목록이 자동으로 업데이트되지 않습니다.

Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법

목록을 빠르고 자동으로 업데이트하려면 이 방법을 따르세요.

3.1:업데이트된 명명된 범위 만들기

먼저 Column 범위의 이름을 추가해야 합니다. 나 새로 추가된 항목을 자동으로 이 이름으로 가져옵니다.
➤ 수식 으로 이동 탭>> 정의된 이름 그룹>> 이름 관리자 옵션.

Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법

그런 다음 이름 관리자 대화 상자가 열립니다.
➤ 새로 만들기 를 클릭합니다. 옵션.

Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법

그 후 이름 수정 마법사가 나타납니다.
➤ 야채 라고 적으십시오. 이름 상자 및 참조 의 다음 공식 상자를 선택하고 마지막으로 확인을 누릅니다. .

=OFFSET(Update!$B$4, 0, 0, COUNTA(Update!$B:$B)-2)

자, 업데이트하세요! 시트 이름은 $B$4 입니다. 이동하려는 참조 셀입니다. 0 행 및 열 인수는 참조 또는 시작 위치에 유지됨을 의미합니다.
COUNTA B열에 모든 유형의 값이 있는 셀 수를 계산합니다. 그런 다음 2 B1 의 데이터세트 제목 때문에 제외됩니다. 및 B3의 열 헤더 . 따라서 야채를 포함하는 세포 수만 얻을 수 있습니다.
이 숫자는 시작 위치 및 OFFSET에서 반환 참조가 됩니다. 항상 업데이트된 명명된 범위를 여기에 반환합니다.

Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법

그런 다음 이름 관리자 로 이동합니다. 마법사.
➤ 닫기를 누릅니다.

Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법

3.2:VBA 코드를 사용하여 데이터 유효성 검사 목록 적용

➤ 시트 이름을 마우스 오른쪽 버튼으로 클릭하고 코드 보기 를 선택합니다. 옵션.

Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법

그러면 코드 창이 나타납니다.

Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법

➤ 다음 코드를 입력하십시오.

Sub worksheet_Change(ByVal newitem As Range)

Dim updatedrange, item
If Not Intersect(newitem, Range("B:B")) Is Nothing Then
For Each item In Range("Vegetables")
updatedrange = updatedrange & "," & item
Next item
With ActiveSheet.Range("D6").Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
       xlBetween, Formula1:=updatedrange
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
End With
End If

End Sub

이 코드는 값의 변경이나 추가가 발생한 경우에만 실행되므로 절차를 Worksheet_Change로 정의했습니다. , 워크시트 개체 입니다. 및 변경 절차입니다. .
새 항목 새 값을 추가할 셀의 주소를 포함하고 이를 범위로 정의했습니다. . updatedrange 의 데이터 유형 및 항목 updatedrange를 할당한 Variant로 간주됩니다. 야채 및 항목의 업데이트된 명명된 범위 이 범위의 각 셀 값에 대한 것입니다.
대상 루프는 업데이트된 범위를 updatedrange에 할당합니다. 그리고 WITH 문은 동일한 개체의 반복을 방지하고 마지막으로 검증을 추가했습니다. .

Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법

이제 메인 시트로 돌아가서 상추 제품을 추가한 후의 효과를 확인할 차례입니다. .
보시다시피, 이제 이 새 항목이 드롭다운 목록에 있습니다.

Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법

이 새 항목을 선택하면 D6 셀에 있습니다. .

Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법

자세히 알아보기: 다른 시트의 데이터 유효성 검사 목록을 사용하는 방법(6가지 방법)

방법-4 :조건부 드롭다운 목록 생성을 위해 명명된 범위 사용

여기서 E6 셀에 드롭다운 목록을 만듭니다. 셀 D6 의 상태에 따라 다릅니다. 이러한 이유로 fruit1 과 같은 두 개의 명명된 범위가 있습니다. 및 야채1 .

Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법

단계 :
➤ Step-01 따르기 방법-1의 .
➤ 다음 코드 작성

Sub Datavalidation4()

If Range("D6") = "Fruits" Then
Range("E6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=fruit1"
Else
Range("E6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=vegetable1"
End If

End Sub

IF-THEN 명령문은 D6 셀의 값을 확인합니다. 과일입니다. 이 값에 대해 목록으로 이름이 지정된 범위 fruit1 을 얻습니다. E6 셀의 목록으로 그렇지 않으면 이름이 지정된 범위가 vegetable1 이 됩니다. E6 셀의 목록으로 .

Excel에서 VBA와 함께 데이터 유효성 검사 목록에 명명된 범위를 사용하는 방법