※ 오늘 Daily Tip은 곽승주님이 제공해주신 내용입니다.
오늘은 유효성 검사에 대한 데일리팁입니다. 유효성 검사는 매우
편리한 기능 중 하나입니다. 사용자가 입력할 수 있는 데이터의 종류나 범위를 검사하여 미리 정한
내용이면 입력을 받아 들이고 아니면 거부할 수 있는 기능입니다.
유효성 검사 항목 중에 목록을 지정하여 목록에 해당하지
않으면 입력을 거부할 수 있습니다. 그러나 목록이 항상 고정된 것이 아니라 경우에 따라 변경되는
경우라면 일일이 목록을 수정해주는 것은 다소 번거로운 점입니다.
오늘의 팁은, 목록을 사용하는 유효성검사에서 목록에 없는
항목을 입력하는 경우 대화상자를 띄워 이를 목록에 추가할 것인지 여부를 묻고 사용자가 ‘OK’하면
이를 목록에 추가하는 내용입니다.
아래의 그림에서 왼쪽의 노란색 영역은 유효성 검사의 목록
내용입니다. 그리고 한 칸 건너 오른쪽의 보라색 영역은 실제 유효성 검사가 적용된 영역입니다.
보통은 오른쪽의 보라색 셀 영역에서 왼쪽 목록에 없는
항목을 입력하는 경우, 유효성 검사에서 지정한 오류메시지를 만나게 될 것입니다. 그러면 입력을 고치거나
취소해야 합니다.
그러나 이번에는 그런 오류메시지와 함께 사용자의 확인을 묻는 대화상자를
떴을 때 사용자가 ‘예’를
선택하여 입력한 항목을 유효성 검사목록에 추가하도록 하겠습니다.
먼저 필자는 위의 워크시트에서 B2:B10
셀 영역을 ‘담당자이름’으로 이름을 부여하였습니다. 그리고 D3:D18 셀 영역을
선택하여 다음 그림과 같이 유효성 검사를 걸어 두었습니다. (아직까지는 코딩을 하지 않았죠)
그리고 오류 메시지 탭에서 다음과 같이 스타일을
‘경고’로 설정하였습니다. 기본 값은 ‘중지’인데 반드시 이번 팁을 위해서는 스타일을 ‘경고’로
설정하여야 합니다.
이제 코딩을 하여 새로 입력한 항목을 목록(즉
‘담당자이름’ 셀 영역)으로 추가하는 일이 남았습니다. 코딩은 워크시트의 내용이 변경될 때마다 실행이
되어야 하므로 해당 시트의 클래스모듈에서 Private Sub
Worksheet_Change(ByVal Target As Range) 이벤트 프로시저를 이용합니다.
코딩은 아래와 같습니다. 아래의 이벤트 프로시저는 앞서의 오류메시지 대화상자에서 ‘예’를
선택하면 실행될 것입니다.
Private Sub
Worksheet_Change(ByVal Target As Range)
'// 위 이벤트 프로시저에서 Target 매개변수는 데이터형이 Range이다.
'// 즉 값이 변경되는 셀 영역을 의미한다.
Dim NewEntry As String
'// 변경되는 셀 영역의 셀 개수가 1이상이면 빠져 나간다
If Target.Cells.Count > 1 Then Exit Sub
'// 변경되는 값이 아무 것도 없으면(즉 DEL키로 지우는 경우) 빠져 나간다
If Target = "" Then Exit Sub
'// Application 개체의 Intersect 메소드를 사용하여
'// 값을 변경하는 셀 영역(Target)이 유효성 검사의 영역에 속하는 가를
'// 확인한다.
If Not Application.Intersect(Target,
Range("유효성검사영역")) Is Nothing Then
'// NewEntry 변수에 Target의 값(즉 변경된 셀의 값)을 저장한다
NewEntry = ""
NewEntry = Target
'// NewEntry 변수 값이 목록('담당자이름' 셀 영역)에 존재하는가를
'// WorksheetFunction의 CountIf()함수를 사용하여 확인한다.
If
Application.WorksheetFunction.CountIf(Sheet1.Range("담당자이름"),
NewEntry) = 0 Then
'// 기존 목록에 없다면 NewEntry변수의 값을 '담당자이름' 셀 영역의 끝에
추가한다.
Sheet1.Range("담당자이름").End(xlDown).Offset(1, 0) =
NewEntry
'// 목록의 내용이 추가된 만큼 다시 같은 이름으로 확장된 셀 영역을 명명한다.
Sheet1.Range("담당자이름").Resize(Sheet1.Range("담당자이름").Rows.Count
+ 1, 1).Name = "담당자이름"
End If
End If
End Sub |
|
OfficeTutor 365