[해결] 엑셀에서 복사·붙여넣기 시 데이터 유효성 검사가 작동하지 않는 문제

엑셀의 데이터 유효성 검사(Data Validation)는 워크시트에 입력되는 데이터를 통제할 수 있게 해주는 매우 강력한 기능입니다. 새로운 데이터를 입력할 때 데이터 유효성 검사를 활용하면 선택한 셀에 원하는 조건을 자유롭게 설정할 수 있습니다. 그러나 이 기능에는 한 가지 치명적인 문제가 있습니다. 바로 값을 복사해서 붙여넣으면 데이터 유효성 검사가 작동하지 않는다는 점입니다.

이해를 돕기 위해 직원 이름(Employee Name), 부서(Department), 그리고 대기자 명단(Waiting List)으로 구성된 한 회사의 데이터셋을 예시로 사용하겠습니다.

엑셀에서 복사·붙여넣기 시 데이터 유효성 검사가 작동하지 않는 문제와 해결 방법

1. 복사·붙여넣기 시 데이터 유효성 검사가 작동하지 않는 원인

이 데이터셋에서는 입력 값을 제한하기 위해 직원 이름 열에 데이터 유효성 검사 기능을 적용해 보겠습니다.

설정 방법 :

• 먼저 직원 이름이 들어 있는 B열 전체를 선택합니다.
• 그다음 데이터 탭에서 데이터 도구 그룹을 찾아 데이터 유효성 검사를 클릭합니다.

대화 상자가 나타납니다.

• 대화 상자에서 설정 탭이 열려 있는지 확인합니다.
• 이후 제한 대상(Allow)에서 유효성 검사 조건을 선택합니다. 여기서는 텍스트 길이를 선택했습니다.
• 다음으로 유효성 검사 범위를 지정합니다. 여기서는 텍스트 길이가 최소 1자부터 최대 8자까지인 데이터만 허용하도록 설정했습니다.

이렇게 하면 데이터 유효성 검사 기능이 적용됩니다.
이제 조건에 맞지 않는 데이터를 직접 입력해 보겠습니다. 대기자 명단에 있는 값 Labuchange를 입력합니다.

유효하지 않은 데이터가 입력되면 경고 메시지가 표시됩니다. 데이터 유효성 검사 조건에 어긋나는 값을 입력했기 때문에 값이 수락되지 않고 경고 메시지가 나타난 것입니다.

그런데 같은 값을 복사해서 유효성 검사가 적용된 열에 붙여넣기하면 어떻게 될까요? 놀랍게도 값이 그대로 수락되며 경고 메시지조차 표시되지 않습니다.

유효성 검사가 복사·붙여넣기 상황에서 무력화되기 때문에 심각한 문제가 될 수 있습니다.

함께 읽으면 좋은 글: 엑셀에서 여러 조건으로 사용자 지정 데이터 유효성 검사 적용하기 (4가지 예제)

관련 자료:

• 엑셀 데이터 유효성 검사 수식에서 IF문 활용하는 방법 (6가지)
• 엑셀에서 색상과 함께 데이터 유효성 검사 사용하기 (4가지 방법)
• 다른 시트의 목록으로 데이터 유효성 검사 만들기 (6가지 방법)
• 엑셀 VBA로 배열에서 데이터 유효성 검사 목록 생성하기
• VBA로 정의된 이름 범위를 활용한 데이터 유효성 검사 목록 사용법

2. VBA로 복사·붙여넣기에도 작동하는 데이터 유효성 검사 만들기

엑셀에서 복사·붙여넣기 시 데이터 유효성 검사가 작동하지 않는 문제를 해결하려면 VBA(Visual Basic for Applications)를 활용하는 것이 사실상 유일한 방법입니다. 지금부터 그 해결 과정을 자세히 설명드리겠습니다.

진행 순서 :

• 먼저 개발 도구 탭을 선택합니다.
• 그다음 Visual Basic을 클릭합니다.

새로운 VBA 편집기 창이 열립니다.

• 코드를 적용하고자 하는 시트를 클릭합니다. 여기서는 VBA라는 이름의 Sheet2에 코드를 적용하겠습니다.

• 왼쪽 드롭다운 목록에서 (일반) 대신 Worksheet를 선택하고, 오른쪽 (선언)에서는 Change를 선택하여 Private Sub 프로시저를 생성합니다.

• 이제 원하는 유효성 검사 규칙에 맞게 아래와 같은 코드를 입력합니다.

실제로 사용한 코드는 다음과 같습니다:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ValidatedCells As Range
    Dim Cell As Range
    Set ValidatedCells = Intersect(Target, Target.Parent.Range("B:B"))
    If Not ValidatedCells Is Nothing Then
        For Each Cell In ValidatedCells
            If Not Len(Cell.Value) <= 8 Then
                MsgBox "The Name """ & Cell.Value & _
                """ inserted in " & Cell.Address & _
                " in column B was longer than 8. Undo!", vbCritical
                Application.Undo
                Exit Sub
            End If
        Next Cell
    End If
End Sub

위 코드에서는 Worksheet_Change라는 Private Sub 프로시저를 만들고, ValidatedCells와 Cell이라는 두 변수를 Range 유형으로 선언했습니다. 그다음 Set 구문을 사용해 유효성 검사를 적용할 범위를 지정했습니다.

여기서는 B열을 검사 대상으로 선택했으며, Range 메서드로 해당 범위를 명시했습니다. 중첩된 IF 문 안에 For 루프를 사용하여, 선택된 범위의 텍스트 길이가 8자를 초과할 수 없다는 조건을 설정했습니다. 만약 이 조건에 맞지 않는 값이 입력되면 MsgBox를 통해 경고 메시지를 담은 경고 상자가 나타나고, 실행 취소(Undo) 기능으로 입력이 자동으로 되돌려집니다.

• 이제 코드를 저장합니다.

• 그다음 워크시트로 돌아가 유효성 검사가 실제로 작동하는지 확인합니다.

D7 셀의 값을 복사해서 B10에 붙여넣어 보았습니다. 데이터 유효성 검사 조건에 따라 오류 경고가 발생하고, 경고 상자가 화면에 나타납니다.

이 방법은 복사·붙여넣기뿐만 아니라 키보드로 직접 입력하는 경우 등 다른 모든 입력 방식에서도 완벽하게 작동합니다.

함께 읽으면 좋은 글: 엑셀에서 다중 선택이 가능한 데이터 유효성 검사 드롭다운 목록 만들기

연습용 통합 문서

아래 파일을 활용해 직접 연습하면서 실력을 키워보세요.

마치며

엑셀에서 복사·붙여넣기 시 데이터 유효성 검사가 작동하지 않는 문제는 다양한 중요한 업무 상황에서 큰 영향을 미칠 수 있습니다. 이번 글에서 소개한 VBA 솔루션이 여러분에게 도움이 되기를 바랍니다. 주제와 관련해 추가로 궁금한 점이 있다면 아래 댓글로 남겨주세요.

관련 글

• 엑셀에서 사용자 지정 수식으로 영숫자만 입력 가능하게 하는 데이터 유효성 검사
• 엑셀 데이터 유효성 검사용 드롭다운 목록 만드는 방법 (8가지)
• 엑셀에서 VBA를 활용한 데이터 유효성 검사 드롭다운 목록 (7가지 응용)
• 엑셀 데이터 유효성 검사 드롭다운 목록 자동 완성 기능 (2가지 방법)
• 필터 기능이 있는 엑셀 데이터 유효성 검사 드롭다운 목록 (2가지 예제)