엑셀 드롭다운 목록 자동완성 데이터 유효성 검사 설정 방법 2가지

데이터 유효성 검사는 엑셀의 강력한 기능 중 하나입니다. 이 기능을 활용하면 사용자가 셀에 임의의 값을 입력하는 것을 막고, 미리 정의된 목록에서만 값을 선택하도록 제어할 수 있습니다. 이번 글에서는 엑셀 드롭다운 목록에 자동완성 기능을 결합한 데이터 유효성 검사를 구현하는 방법을 단계별로 알아보겠습니다.

엑셀에서 드롭다운 목록 자동완성 데이터 유효성 검사를 구현하는 2가지 방법

드롭다운 목록의 자동완성 데이터 유효성 검사를 구현하는 두 가지 방법을 소개합니다. 설명에는 아래와 같은 데이터 세트를 사용합니다.

방법 1. VBA 코드와 콤보 상자(Combo Box) 컨트롤을 활용한 자동완성

ActiveX 컨트롤 도구에 사용자 지정 VBA 코드를 삽입하여 드롭다운 목록에서 자동으로 데이터 유효성 검사가 작동하도록 만드는 방법입니다.

1단계:

• 먼저 리본 메뉴에 개발 도구 탭을 추가해야 합니다. 파일 > 옵션으로 이동합니다.
• Excel 옵션에서 리본 사용자 지정을 선택합니다.
• 개발 도구 항목에 체크한 후 확인을 누릅니다.

2단계:

• 개발 도구 탭에서 삽입을 선택합니다.
• ActiveX 컨트롤 목록에서 콤보 상자를 선택합니다.

3단계:

• 데이터 세트 위에 콤보 상자를 배치합니다.
• 마우스 오른쪽 버튼을 클릭하고 목록에서 속성을 선택합니다.

4단계:

• 속성 창에서 이름(Name)을 TempComboBox로 변경합니다.

5단계:

• 시트 이름 탭으로 이동합니다.
• 목록에서 코드 보기(View Code)를 선택합니다.

VBA 명령 모듈이 열리면 해당 모듈에 VBA 코드를 입력해야 합니다.

6단계:

• 아래의 VBA 코드를 복사하여 모듈에 붙여넣습니다.

Private Sub Wrksht_SelectionChange(ByVal Target As Range)
    Dim combox_1 As OLEObject
    Dim str_1 As String
    Dim ws_1 As Worksheet
    Dim arr_1
    
    Set ws_1 = Application.ActiveSheet
    On Error Resume Next
    Set combox_1 = ws_1.OLEObjects("TempComboBox")
    With combox_1
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
    End With
    If Target.Validation.Type = 3 Then
        Target.Validation.InCellDropdown = False
        Cancel = True
        str_1 = Target.Validation.Formula1
        str_1 = Right(str_1, Len(str_1) - 1)
        If str_1 = "" Then Exit Sub
        With combox_1
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 5
            .Height = Target.Height + 5
            .ListFillRange = str_1
            If .ListFillRange = "" Then
                arr_1 = Split(str_1, ",")
                Me.TempComboBox.List = arr_1
            End If
            .LinkedCell = Target.Address
        End With
        combox_1.Activate
        Me.TempComboBox.DropDown
    End If
End Sub
Private Sub TempComboBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
        Case 9
            Application.ActiveCell.Offset(0, 1).Activate
        Case 13
            Application.ActiveCell.Offset(1, 0).Activate
    End Select
End Sub

7단계:

• VBA 코드를 저장한 후 데이터 세트로 돌아갑니다. 개발 도구 탭에서 디자인 모드를 해제합니다.

8단계:

• C5 셀을 선택합니다.
• 데이터 탭의 데이터 도구 그룹으로 이동합니다.
• 목록에서 데이터 유효성 검사를 선택합니다.

9단계:

• 데이터 유효성 검사 창이 나타나면 제한 대상 필드에서 목록을 선택합니다.
• 원본 필드에 참조할 값 범위를 지정합니다.
• 그런 다음 확인을 누릅니다.

10단계:

• Selection 열의 아무 셀이나 클릭하고 첫 글자 하나를 입력해 봅니다.

글자를 입력하면 해당 셀에 일치하는 제안 목록이 자동으로 표시됩니다.

이제 제안된 목록에서 원하는 항목을 선택하여 나머지 셀들도 모두 완성하면 됩니다.

함께 읽으면 좋은 글: 엑셀에서 VBA로 데이터 유효성 검사 드롭다운 목록 활용하기(7가지 응용)

방법 2. ActiveX 컨트롤의 콤보 상자만으로 구현하는 자동완성

이번에는 VBA 코드 없이 ActiveX 컨트롤의 콤보 상자만 사용하여 자동 데이터 유효성 검사를 구현합니다.

1단계:

• 개발 도구 탭에서 삽입 그룹을 선택합니다.
• ActiveX 컨트롤에서 콤보 상자를 선택합니다.

2단계:

• 데이터 세트의 빈 공간에 콤보 상자를 배치합니다.
• 마우스 오른쪽 버튼을 클릭합니다.
• 목록에서 속성을 선택합니다.

3단계:

• Linked Cell(연결된 셀) 필드에 C5를 입력합니다. 선택한 데이터가 C5 셀에 표시됩니다.
• ListFillRange 필드에 $B$5:$B$9를 입력합니다.
• MatchEntry 필드에서 1-fmMatchEntryComplete를 선택한 후 변경 사항을 저장합니다.

4단계:

• 개발 도구 탭에서 디자인 모드를 해제합니다.

5단계:

• 콤보 상자에 아무 글자나 입력하면 자동완성 제안이 나타납니다. 최종적으로 선택한 데이터가 C5 셀에 표시됩니다.

함께 읽으면 좋은 글: 엑셀에서 데이터 유효성 검사 드롭다운 목록 만드는 방법(8가지)

마무리

이번 글에서는 엑셀에서 드롭다운 목록 데이터 유효성 검사를 수행하고, 여기에 자동완성 기능을 추가하는 두 가지 방법을 살펴보았습니다. VBA 코드를 활용하는 방법과 ActiveX 컨트롤만으로 구현하는 방법 중 상황에 맞는 방식을 선택해 활용해 보시기 바랍니다. 더 많은 엑셀 팁은 Exceldemy.com에서 확인하실 수 있으며, 궁금한 점이나 제안 사항은 댓글로 남겨주세요.

관련 글

• 엑셀에서 표(Table)로 데이터 유효성 검사 목록 만들기(3가지 방법)
• 엑셀에서 다중 선택이 가능한 데이터 유효성 검사 드롭다운 목록 만들기
• 엑셀에서 한 셀에 여러 데이터 유효성 검사 규칙 적용하기(예시 3가지)
• Excel VBA로 데이터 유효성 검사 목록 기본값 설정하기(매크로 및 사용자 폼)
• 다른 셀의 값을 기준으로 하는 엑셀 데이터 유효성 검사
• 엑셀에서 여러 조건에 맞는 사용자 지정 데이터 유효성 검사 적용하기(예시 4가지)
• 엑셀 데이터 유효성 검사로 영문·숫자만 입력 허용하기(사용자 지정 수식)