VBA 스크립트를 활용한 엑셀 데이터 유효성 검사 수정 방법

VBA 스크립트를 활용한 Excel 데이터 유효성 검사 수정 방법

엑셀의 데이터 유효성 검사는 데이터를 입력하거나 수정할 때 특정 조건을 검사하여 유효한 값을 입력하도록 돕는 유용한 기능입니다. 그러나 유효성 검사 기능이 때로는 오류 메시지를 발생시키고, 이로 인해 입력 오류를 수정하는 것이 번거로울 수 있습니다. VBA 스크립트를 사용하면 이러한 문제를 보다 효과적으로 해결할 수 있습니다. 

이번 글에서는 VBA 스크립트를 활용하여 엑셀 데이터 유효성 검사를 수정하는 방법을 알아보겠습니다.

칼퇴키트 바로가기

칼퇴키트, 업무용 도구모음

1. 문제점

엑셀의 기본 유효성 검사 기능은 입력값이 지정된 조건과 일치하지 않을 때 오류 메시지를 띄우고, 사용자가 입력값을 수정할 수 있도록 합니다. 하지만, 이러한 방식은 때때로 사용자가 입력한 값을 자동으로 수정하거나 변경하기 어렵게 만들 수 있습니다.

예를 들어, "A", "B", "C" 중 하나의 값을 허용하는 유효성 검사를 설정했다고 가정해 봅시다. 사용자가 유효하지 않은 값을 입력할 경우, 기본 유효성 검사 기능은 오류 메시지를 표시하고 사용자가 값을 수정해야 합니다.

---

2. 해결 방법

VBA 스크립트를 사용하면 유효성 검사 조건을 만족하지 않는 입력값에 대해 자동으로 수정하거나 적절한 메시지를 표시할 수 있습니다. 아래 코드는 특정 열(여기서는 열 1)에서 입력된 데이터가 "A", "B", "C" 중 하나인지 확인하고, 조건을 만족하지 않으면 자동으로 입력값을 되돌립니다.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Oldvalue As String
    Dim Newvalue As String
    Dim ValidList As String
    Dim ValidTest As Boolean

    On Error GoTo Exitsub

    ' 특정 열에서만 유효성 검사를 실행
    If Target.Column = 1 Then
        ' 여러 셀에 입력된 경우
        If Target.Cells.Count > 1 Then GoTo MultiEntry
        ' 빈 셀은 허용
        If Target.Value = "" Then GoTo AllowBlank

        Application.EnableEvents = False
        Newvalue = Target.Value
        Application.Undo
        Oldvalue = Target.Value
        ValidList = "A,B,C"
        ValidTest = False

        ' 유효성 검사
        For Each Item In Split(ValidList, ",")
            If Oldvalue = Item Then
                ValidTest = True
                Exit For
            End If
        Next Item

        If ValidTest = True Then GoTo AllowCell
        Target.Value = Oldvalue

ErrMsg:
        MsgBox "You must enter a value from the approved list." & vbNewLine & "The approved list is: " & ValidList, vbCritical, "Invalid Entry"
        AllowBlank:
        Application.EnableEvents = True
        Exit Sub

AllowCell:
        Application.EnableEvents = True
        Exit Sub

MultiEntry:
        MsgBox "You have entered data into several cells" & vbNewLine & "Please enter data into one cell only", vbInformation, "Error"
        Application.EnableEvents = True
    End If

Exitsub:
    Application.EnableEvents = True
End Sub

 

---

3. 코드 설명

- `Worksheet_Change` 이벤트는 셀의 값이 변경될 때 자동으로 호출됩니다.
- `If Target.Column = 1 Then` 구문은 유효성 검사를 특정 열(여기서는 열 1)에서만 적용하도록 설정합니다.
- `If Target.Cells.Count > 1 Then GoTo MultiEntry` 구문은 여러 셀에 값을 입력했을 경우 오류 메시지를 표시합니다.
- `Application.Undo`와 `Oldvalue`를 사용하여 입력값을 이전 상태로 되돌립니다.
- `For Each Item In Split(ValidList, ",")` 구문을 통해 유효한 값 목록을 순회하며 입력값이 유효한지 검사합니다.
- 유효성 검사를 통과하지 못한 경우 `MsgBox`를 통해 오류 메시지를 표시하고, 입력값을 되돌립니다.

---

4. VBA 코드 적용 방법

1. 엑셀 워크북에서 `Alt + F11`을 눌러 VBA 편집기를 엽니다.
2. 왼쪽의 프로젝트 탐색기에서 원하는 워크시트를 선택합니다.
3. 선택한 워크시트에 위의 VBA 코드를 붙여넣고 저장합니다.

---

5. 마치며

엑셀의 기본 유효성 검사 기능은 유용하지만, 때로는 VBA 스크립트를 사용하여 더 세밀한 제어가 필요할 수 있습니다. 이번 글에서는 VBA 스크립트를 활용하여 데이터 유효성 검사를 수정하는 방법을 살펴보았습니다. 이 방법을 사용하면 데이터 입력 오류를 자동으로 수정하거나 경고 메시지를 적절히 표시하여 더욱 효율적인 데이터 관리가 가능합니다.

포스팅이 도움이 되셨다면 구독, 공감, 댓글 부탁드려요!

행복한 하루 되세요!