엑셀 콤보박스 - 리스트 범위가 변하는 동적 콤보박스(양식컨트롤)

엑셀 양식을 만들때 흔히 많이 쓰는 컨트롤이 콤보상자(Combo Box) 입니다. 흔히 드롭다운(Drop Down) 박스라고 하기도 합니다. 

개발도구에는 양식컨트롤과 ActiveX 컨트롤 2가지의 컨트롤이 있는데 그중에서 VBA를 잘 알지 못해도 흔히 사용하는 양식컨트롤의 콤보상자를 양식에 넣는 방법과 콤보상자의 입력범위가 추가되거나 줄어들때 자동으로 범위가 늘어났다 줄어드는 수식에 대해  설명 드립니다.

콤보상자(Combo Box) 삽입하기

1. 리본메뉴 [개발도구] - [삽입] - [양식컨트롤 콤보상자]를 선택합니다.

2. [콤보상자]를 넣고 싶은곳에 그려 넣습니다.

3. Alt키를 누르고 사각형을 그리면 셀의 모서리(교차점)에 정확하게 삽입 됩니다. 

이름정의하기

품명을 콤보상자의 입력범위에 들어갈 셀범위를 이름정의 합니다.

이때 단순히 A2:A9를 입력하지 마시고 아래의 수식을 입력하세요. 셀범위만 입력하는 경우는 리스트가 추가되는 경우 다시 범위를 입력해야 합니다. 이런 번거러움을 줄이기 위해서 이름정의를 하여 동적(가변형) 셀범위를 만들어 줍니다. 

1. 리본메뉴 [수식] - [이름정의]를 클릭합니다.

2. [새 이름] 창에 [이름]을 입력하고 [참조대상]에 아래의 수식을 입력합니다.

3. 수식 = OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1)

4. [확인] 버튼을 클릭하면 "품명"이라는 이름이 정의 됩니다.

☞ 수식설명

1. COUNTA 함수 : 품명이 있는 A열의 비어있지 않는 셀의 개수를 반환합니다. (주의 : A열 전체를 참조 할때는 A열에는 품명외 다른 값이 입력되면 안됨)

2. OFFSET 함수 :  참조영역 A1셀에서 행방향으로 1, 열방향으로 0 떨어진 위치에서 COUNTA - 1 만큼의 행수의 참조 범위를 반환 합니다.

3. A열에 품명이 추가되면 COUNTA 함수에 의해 참조 범위가 변합니다.

컨트롤 서식 설정

1. 이름정의 까지 완료 되었으면 콤보상자에 마우스를 대고 우측 클릭합니다.

2. [단축메뉴] 하단에 [컨트롤 서식]을 클릭합니다.

3. [컨트롤 서식] 창에서 [입력범위]에 이름정의 된 "품명"을 [셀 연결]에는 C1셀을 클릭합니다.

4. [셀 연결]은 콤보상자에서 목록(리스트)가 선택되었을때 선택 번호를 반환합니다.

5. [목록 표시 줄 수]는 콤보상자 우측의 역삼각형 버튼을 클릭했을때 나타나는 목록 개수 입니다.

동적(가변) 콤보상자

품명이 추가되면 콤보상자의 목록도 자동으로 추가되어 [입력 범위]를 수정할 필요가 없습니다.

셀 연결 값

1. 양식컨트롤의 콤보상자는 선택한 목록의 순번을 연결된 셀에 표시합니다.

2. 지정셀에 선택한 목록의 값을 나타내려면 INDEX 함수나 OFFSET 함수로 값을 반환 합니다.

3. 여기서는 INDEX 함수를 사용해서 수식 = INDEX(A:A,C1+1) 을 입력합니다.

뭔가 복잡합니다. 숫자(순번)를 반환하지 말고 선택 목록값을 나타내면 활용도가 더 높을 것 같은데 왜 이렇게 만들었을 까요? 다 이유가 있겠지만 잘 모르겠네요. ^^

관련글 보기

유효성검사 드롭다운 목록

인카사드(카메라 기능)

03

첨부파일 보기

동적 콤보상자 리스트.xlsx