엑셀 데이터 유효성 목록상자 기본값 설정 방법 :: 실무기초
데이터 유효성 목록 기본값 설정 목차 바로가기
영상강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 24강 데이터 유효성 목록상자 기본값 설정예제파일
데이터 유효성 목록 상자란 지정한 셀에 입력되는 값이 특정 범위(또는 목록) 내 값만 입력 가능하도록 제한하는 기능입니다.
데이터 유효성 목록 상자 활용 예제 예를들어, [ 상의, 하의, 자켓, 블라우스 ] 라는 4개의 제품 구분으로만 값을 입력해야 할 경우, 데이터 유효성 목록 상자 기능을 사용하면 위 4개의 값 중 하나만 선택해서 입력할 수 있게 됩니다.
- 예제파일의 C9 셀을 선택한 뒤, [ 데이터 - 데이터 유효성 검사 - 데이터 유효성 검사 ] 로 이동합니다.
데이터 - 데이터 유효성 검사 항목으로 이동합니다. - 제한 대상으로 '목록'을 선택한 뒤, 원본 범위로 H9:H18 범위를 선택합니다. 확인 버튼을 눌러 창을 닫습니다.
제한대상으로 '목록'을 선택한 뒤, 원본 범위를 설정합니다. - 데이터 유효성 검사 목록상자가 적용되었습니다.
데이터 유효성 검사 목록 상자가 적용되었습니다. - 원본 범위에 새로운 데이터가 추가될 시, 범위가 자동으로 확장되도록 하려면 표기능이나 동적범위를 활용합니다. 동적범위에 대한 자세한 설명은 아래 OFFSET 동적범위 기초 강의를 참고하세요.
데이터 유효성 목록 상자의 기본값을 설정할 수 있을까요? 예를들어, 아무런 값도 선택되지 않았을 경우, '----- 값을 선택하세요 ------' 와 같은 안내 문구를 출력하는 것 처럼요.
셀의 정렬기능을 활용하면 데이터 유효성 검사 목록 외에도 주변 셀이 빈칸일 경우에 기본값을 손쉽게 출력할 수 있습니다.
- 알파벳 'C'를 클릭하여 C열을 전체선택 한 뒤, 우클릭 - 삽입을 클릭하여 새로운 열을 추가합니다. (단축키 Ctrl + Shift + +)
C열을 전체 선택한 뒤, 우클릭 - 삽입을 클릭하여 새로운 열을 삽입합니다. - C9셀을 선택한 뒤, [ '----- 값을 입력하세요 ----- ] 를 입력합니다. (앞에 작은따옴표(')를 추가하는 것에 주의합니다)
새로 추가된 열의 셀을 클릭한 후, 기본값을 입력합니다. - 키보드 Ctrl 키를 누른채로 D9, D12, D15, ... D27 셀까지 기존 데이터유효성 목록이 설정 된 셀을 모두 선택합니다. 셀을 우클릭하여 '셀 서식'으로 이동합니다.
기존 데이터유효성 목록이 적용된 셀 선택 후, 셀 서식으로 이동합니다. - '테두리'로 이동한 뒤, 왼쪽 테두리를 '없음'으로 설정합니다. 확인을 눌러 창을 닫습니다.
왼쪽 테두리를 없음으로 변경합니다. - C9 셀을 선택한 뒤, 우클릭하여 셀서식으로 이동합니다. 이번에는 위쪽/아래쪽/왼쪽 테두리를 추가한 뒤, 확인을 눌러 창을 닫습니다.
셀의 위쪽, 아래쪽, 왼쪽 테두리를 설정합니다. - C9셀을 복사합니다. 이후 C9, C12, C15, ... C27 까지 범위를 선택한 뒤, Ctrl + V 로 복사한 값을 붙여넣기 합니다.
C9셀을 복사한 뒤 다른 남아있는 범위에 붙여넣기 합니다. - C열을 전체선택한 뒤, 홈탭의 맞춤 그룹에서 '들여쓰기' 버튼을 클릭하여 한번 들여쓰기 합니다.
C열을 전체선택 한 뒤 들여쓰기 합니다. - C열이 선택된 상태로 우클릭하여 열 너비로 이동합니다. 열 너비를 0.05로 변경합니다.
C열의 열너비를 0.05로 최대한 좁게 변경합니다. - 데이터 유효성 목록 기본값 설정이 완료되었습니다.
데이터유효성 검사 기본값 설정이 완료되었습니다.
셀 정렬기능을 활용한 기본값 표시방법에는 제한사항이 있습니다. 그것은 바로, 유효성 검사가 적용된 셀이 병합되어 있을 경우 기본값이 표시되지 않는다는 것 인데요. 그럴 경우 아래 두번째, 세번째 방법을 이용합니다.
유효성검사 목록상자가 적용된 셀이 병합되어 있을 경우 첫번째 방법을 사용할 수 없습니다. - 데이터 유효성 검사가 적용된 셀을 선택한 뒤, 데이터 - 데이터유효성 검사 - 데이터 유효성 검사로 이동합니다.
데이터 - 데이터유효성 검사 설정으로 이동합니다. - '오류 메시지' 탭으로 이동한 뒤, 유효하지 않은 데이터를 입력하면 오류 메시지 표시 체크박스를 비활성화 합니다. '확인'버튼을 눌러 창을 종료합니다.
오류메시지 탭으로 이동한 뒤, 체크박스를 비활성화 합니다. - 셀에 기본값을 입력합니다.
기본값을 입력합니다. - 다시 데이터유효성 검사 설정으로 이동한 뒤, 체크박스를 활성화합니다. '확인'버튼을 눌러 종료합니다.
다시 유효성 목록 설정으로 이동한 뒤, 체크박스를 활성화합니다. - 데이터 유효성 목록 기본값 설정이 완료되었습니다. 단, 두번째 방법은 유효성 검사 값을 입력하기 전 최초 1회만 기본값이 출력되며, 한번 값이 선택되면 그 이후에는 기본값이 출력되지 않는다는 단점이 있습니다.
데이터 유효성 목록 기본값 설정이 완료되었습니다.
매크로를 사용하면 병합된 셀에 적용된 데이터 유효성 목록에서도 기본값을 표시할 수 있습니다. 완성된 전체 코드를 제공해드리니 필요한 부분만 적절히 수정하시면 아주 간단하게 데이터 유효성 목록 기본값을 적용할 수 있습니다.
- 개발도구 - Visual Basic 을 클릭하거나 단축키 Alt + F11 키를 눌러 매크로 편집기로 이동합니다. 개발도구가 보이지 않을 경우, 아래 개발도구 활성화하는 방법 관련 포스트를 확인하세요.
개발도구 - Visual Basic 편집창으로 이동합니다. - 데이터 유효성 목록상자의 기본값을 적용할 시트를 더블클릭하면 시트 모듈이 활성화 됩니다.
해당 시트를 더블클릭하면 시트모듈이 활성화됩니다. - 아래 명령문을 복사한 뒤, 시트 모듈안에 붙여넣기 합니다.
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim sRng As String: Dim vRngs As Variant: Dim vRng As Variant Dim sMsg As String Application.EnableEvents = False ' 데이터 유효성 검사가 활성화된 셀 주소를 쉼표(,) 로 구분하여 입력하세요. sRng = "셀1, 셀2, 셀3" ' 데이터 유효성 검사 기본값으로 출력 될 메세지를 입력하세요. sMsg = "------ 값을 선택하세요 ----- " vRngs = Split(sRng, ",") For Each vRng In vRngs If Trim(vRng) = Target.Resize(1, 1).Address(0, 0) Then On Error GoTo EH: If Len(Target.Text) = 0 Then On Error Resume Next Target.Validation.ShowError = False Target.Value = sMsg Target.Validation.ShowError = True Exit For End If End If Next EH: Application.EnableEvents = True End Sub
- sRng = "셀1, 셀2, 셀3" 을 실제 데이터 유효성 목록이 적용된 셀 주소로 변경합니다. (예제파일 기준 D9, D12, D15, ... D27)
sRng = "D9, D12, D15, D18, D21, D24, D27"
- sMsg = "------ 값을 선택하세요 ----- " 을 실제 출력할 데이터 유효성 목록상자의 기본값으로 변경합니다.
sMsg = "------ ★★ 값을 선택하세요 ★★ ------"
- 이후 Sheet1 로 이동한 뒤 데이터유효성 목록 상자의 값을 지우면 매크로에 입력한 기본값이 표시됩니다.
매크로를 활용한 데이터유효성 목록 기본값 설정이 완료되었습니다.
- 예제파일의 C9 셀을 선택한 뒤, [ 데이터 - 데이터 유효성 검사 - 데이터 유효성 검사 ] 로 이동합니다.
