엑셀에서 목록 상자(Form Control) 항목이 비어 있을 때 해결

#2 엑셀 오류 가이드

엑셀에서 목록 상자(Form Control) 항목이 비어 있을 때 해결 — 완벽 가이드

이세계의엑셀 2025. 4. 21. 22:50

첫 화면에서 엑셀에서 목록 상자(Form Control) 항목이 비어 있을 때 해결 문제가 나타나면 낭패다. 이 글은 같은 증상을 겪는 사용자에게 엑셀에서 목록 상자(Form Control) 항목이 비어 있을 때 해결 방법을 5 000자 이상 분량으로 정리해 한 번에 끝낼 수 있게 돕는다.

문제가 드러나는 순간

목록 상자를 클릭했을 때 아무 항목도 표시되지 않거나 드롭다운이 빈 셀처럼 보인다면 업무 흐름이 완전히 끊긴다. 증상은 다음과 같이 나타난다.

셀 연결(Link) 범위는 정상인데 목록이 표시되지 않음
시트 복사 ↔ 붙여넣기 이후 목록이 초기화
데이터가 동적으로 변하지만 목록 상자는 갱신되지 않음

원인 분석

실무에서 자주 확인되는 근본 원인은 아래 네 가지가 가장 많다.

구분	구체적 원인	세부 설명
1	입력 범위 오타	범위 명칭 또는 절대/상대 참조 오류
2	숨은 행·열	데이터가 숨겨져 목록에서 인식되지 않음
3	이름 정의 누락	‘이름 관리자’에 정의된 범위가 없음
4	동적 범위 미사용	행 추가 시 목록이 자동 확장되지 않음

빠른 진단 체크리스트

Ctrl + F3로 이름 관리자를 열어 목록 범위가 정확한지 확인
숨은 행·열 존재 여부를 Ctrl + Shift + 9 / Ctrl + Shift + 0으로 즉시 해제
셀의 데이터 유효성 검사와 혼동되지 않았는지 점검
시트 보호가 켜져 있는지 검토 → 시트 보호 해제 경로로 확인

해결 방법

1. 데이터 범위 재지정

데이터가 있는 영역을 선택
수식 → 이름 정의(N) 단축키 Ctrl + F3
새로운 이름(ListRange)을 만들어 =OFFSET($B$2,0,0,COUNTA($B:$B)-1,1) 식으로 설정
목록 상자를 우클릭 → 서식 컨트롤 → 입력 범위에 ListRange 입력

TIP
OFFSET 대신 INDEX 조합으로 무거운 통합문서의 속도를 높일 수 있다.

2. 숨은 행·열 복구

행 번호·열 문자를 모두 선택한 뒤 숨기기 취소
필터가 걸려 있다면 데이터 → 필터 해제로 초기화

3. 시트 보호 해제 후 재연결

시트 보호가 목록 상자 동작을 차단하는 사례가 많다.

Sub UnprotectAndRelink()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ws.Unprotect Password:="yourPW"
    With ws.Shapes("Drop Down 1").ControlFormat
        .ListFillRange = "ListRange"
        .LinkedCell = "A1"
    End With
    ws.Protect Password:="yourPW", UserInterfaceOnly:=True
End Sub

UserInterfaceOnly 옵션을 사용하면 VBA는 자유롭게 컨트롤을 변경하되 사용자는 시트를 보호 상태로 유지할 수 있다.

4. VBA로 동적 업데이트 자동화

새 행·열이 추가될 때마다 사용자가 직접 범위를 조정하지 않도록 워크시트 이벤트로 관리한다.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        On Error Resume Next
        Me.Shapes("Drop Down 1").ControlFormat.ListFillRange = _
            "OFFSET($B$2,0,0,COUNTA($B:$B)-1,1)"
        On Error GoTo 0
    End If
End Sub

Worksheet_Change 이벤트는 열 B 데이터가 바뀔 때마다 실행
오류 발생 시 일단 무시하고 다음 입력을 기다리므로 사용 경험이 부드럽다

고급 팁 및 예방 전략

이름 범위 대신 표(Table) 사용

표를 만들면 목록 상자가 테이블이 확장될 때 자동으로 해당 영역만큼 늘어난다.

데이터 범위 선택 → Ctrl + T
표 이름을 tblList로 변경
입력 범위에 tblList[항목] 지정

함수 기반 드롭다운

데이터 유효성 검사를 조합해 가시성·성능을 모두 잡을 수 있다.

' 동적 드롭다운: 빈 셀 제외
=FILTER($B$2:$B$100,$B$2:$B$100<>"")

외부 연결 방지

시트 복·붙 시 참조가 다른 통합문서로 바뀌지 않도록 상대 참조를 써 두었다면 절대 경로($)로 고정한다.

핵심 요약과 추가 팁

이번 글에서는 엑셀에서 목록 상자(Form Control) 항목이 비어 있을 때 해결 과정을 원인 파악부터 VBA 자동화까지 자세히 살펴봤다.

오타·숨은 행·이름 정의·동적 범위 네 가지 근본 원인을 표로 정리
체크리스트로 빠른 진단 루틴 제공
OFFSET·INDEX 기반 동적 범위와 Worksheet_Change 이벤트 활용 코드 제시
표(Table)·함수 조합 등 예방 전략까지 덧붙여 실무에서 바로 적용 가능하다.

마지막으로, 항상 새 데이터를 입력한 직후 목록이 정상적으로 업데이트되는지 테스트해서 엑셀에서 목록 상자(Form Control) 항목이 비어 있을 때 해결 문제를 완전히 종결하자.

저작자표시 비영리 변경금지