Excel [지식인 해결 시리즈] 010. 콤보박스에 여러 시트에 있는 데이터 입력하기

전체 목차
001. lookup 함수를 이용한 양도세 구간 구하기
002. 공백 셀 제거 후 정렬
003.데이터 분석 후 행 추가하기 (나누기, 나머지, 정수)
004. 목록상자, 토글버튼 연동
005. 다른 시트에 있는 특정 데이터를 복사해오기
006. 근무시간에서 특정시간 구하기 (야간 근무 인정 시간)
007. 상반기/하반기 매출 집계(SUMIF)
008. 콤보박스 활용하여 필터 변경하기
009. 특정 위치에 있는 데이터만 추출하기 (address, indirect)

 

포스트 목차.
01. 문제
02. 문제해결 방법
03. 예제 및 설명

01. 문제

 

 

문제는 드롭다운(콤보박스)에 다른 시트를 포함하여 여러 범위에 분산되어 있는 데이터를 입력하는 방법을 묻고 있다.

 

02. 문제해결 방법

 

엑셀에서 제공하는 콤보박스는 두 가지 종류가 있다. 해당 콤보박스 컨트롤에 대해서는 이전에 다룬적이 있으므로 이전 포스트를 참고하면 도움이 될 것 같다.

 

https://eggdrop.tistory.com/22

Excel 매크로 (VBA) - 010. 콤보 상자 컨트롤 (Excel Form vs. ActiveX)

사실 위의 포스트를 읽으면 문제를 해결 할 수 있지만 여기서는 데이터가 여러 시트에 분산되어 있다는 점에 초점을 두고 문제를 해결해보고자 한다.

 

콤보박스는 엑셀 폼 컨트롤을 기준으로 설명하려고 한다.

 

콤보박스에 내용을 채워넣는 과정을 시작하기 위한 방법이 필요한데, 본 포스트에서는 버튼에 이벤트 핸들러 루틴을 추가하여 처리하려고 한다.

 

버튼을 누르면, 콤보박스의 내용이 초기화 되고, 엑셀 파일의 특정 영역의 데이터를 불러와서 콤보박스에 넣는 작업을 하는 식으로 동작한다.

 

콤보박스(엑셀 폼 컨트롤 또는 엑셀 양식 컨트롤)를 제어하는 방법에 대해서 간단히 살펴보고 넘어가자

 

1) 콤보박스에 들어 있는 아이템 제거하기

    [workSheet 객체].Shapes("컨트롤 이름").ControlFormat.RemoveAllItems

   

    사용 예) ActiveSheet.Shapes("드롭다운 1").ControlFormat.RemoveAllItems

   

2) 콤보박스에 아이템 넣기

     [workSheet 객체].Shapes("컨트롤 이름").ControlFormat.AddItem "아이템"

 

     사용 예) ActiveSheet.Shapes("드롭다운 1").ControlFormat.AddItem "아이템"

 

문제 해결을 위해서는 다음 과정을 거치면된다.

1) 콤보박스에 들어 있는 아이템 제거하기

2) 콤보박스에 들어갈 아이템이 있는 영역 Range객체나 다른 방식으로 얻어오기

3) 콤보박스에 아이템 넣기

 

03. 예제 및 설명

 

예제 (1) 변수이용하여 삽입하기

Sheet1에는 다음과같은 데이터들이 있으며, 하나의 콤보박스와 3개의 버튼이 삽입되어 있다.

Sheet2와 Sheet3에는 다음과 같은 데이터들이 들어 있다. 

 

일단 Sheet1에 있는 콤보박스 초기화 버튼을 누르면 Sheet1의 A1:A4, Sheet2의 A1:A4, Sheet3의 A1:A4에 있는 데이터가 콤보박스에 들어가게 하는 코드를 작성해보자.

 

버튼 컨트롤에 대해 더 자세히 알고 싶으면 다음 링크를 참고하자

https://eggdrop.tistory.com/21

Excel 매크로 (VBA) - 009. 버튼 컨트롤 (Excel Form vs. ActiveX )

 

Sub 단추2_Click()
  '콤보박스를 제어하기 위한 ControlFormat을 cf라는 변수에 할당
  Set cf = ActiveSheet.Shapes("드롭다운 1").ControlFormat
  '콤보박스 초기화 실행
  cf.RemoveAllItems
 
  
  'Sheet1, Sheet2, Sheet3을 지시하기 위한 변수를 선언하고 값 할당
  Dim sh1 As Worksheet
  Dim sh2 As Worksheet
  Dim sh3 As Worksheet
  Set sh1 = Worksheets("sheet1")
  Set sh2 = Worksheets("sheet2")
  Set sh3 = Worksheets("sheet3")
  
  '콤보박스에 넣으려고 하는 영역 저장을 위한 변수를 선언하고 값 할당
  Dim rng1 As Range
  Dim rng2 As Range
  Dim rng3 As Range
  Set rng1 = sh1.Range("A1:A4")
  Set rng2 = sh2.Range("A1:A4")
  Set rng3 = sh3.Range("A1:A4")
  
  'sheet1 영역을 순회하며 값을 콤보박스에 넣는다.
  For Each Item In rng1.Cells
    cf.AddItem Item.Value
  Next Item
  'sheet2 영역을 순회하며 값을 콤보박스에 넣는다.
  For Each Item In rng2.Cells
    cf.AddItem Item.Value
  Next Item
  'sheet3 영역을 순회하며 값을 콤보박스에 넣는다.
  For Each Item In rng3.Cells
    cf.AddItem Item.Value
  Next Item
 
End Sub

 

위의 코드는 시트 지정 > 영역 지정 > 영역에 있는 데이터 순회 후 콤보박스에 입력 절차를 거쳐 콤보박스를 초기화한다.

 

버튼을 누른면 콤보박스에 데이터가 들어가고, 다음과 같이 들어간 데이터를 확인할 수 있다. 

 

예제 (2) 배열이용하기

위의 코드(1)에서는 서로 다른 시트에 있는 영역(Range)를 순회하기 위해 For문의 세번 사용했는데, 이번에는 Range객체를 저장할 배열을 사용해보자.

 

다만 데이터를 입력할 때, Sheet3의 A1:A3까지만 입력되도록 지정해보자. Sheet1, Sheet2는 이전 예제와 동일하다.

 

Sub 단추3_Click()
  '콤보박스를 제어하기 위한 ControlFormat을 cf라는 변수에 할당
  Set cf = ActiveSheet.Shapes("드롭다운 1").ControlFormat
  '콤보박스 초기화 실행
  cf.RemoveAllItems
  'Sheet1, Sheet2, Sheet3을 지시하기 위한 변수를 선언하고 값 할당
  Dim sh1 As Worksheet
  Dim sh2 As Worksheet
  Dim sh3 As Worksheet
  Set sh1 = Worksheets("sheet1")
  Set sh2 = Worksheets("sheet2")
  Set sh3 = Worksheets("sheet3")
  '배열 선언
  Dim rngs(1 To 3) As Range
  
  '배열에 범위 지정
  Set rngs(1) = sh1.Range("A1:A4")
  Set rngs(2) = sh2.Range("A1:A4")
  Set rngs(3) = sh3.Range("A1:A3")
   
  '배열 및 배열에 저장된 Range를 순회하며 데이텀 입력
  For Each Rng In rngs
    For Each Item In Rng.Cells
        cf.AddItem Item.Value
    Next Item
  Next Rng
 
End Sub

위의 코드는 (1)번 예제와 유사한 작업을 하지만, For문을 3번 작성하는 대신에, 중첩 For문을 1번만 작성하여 데이터를 입력할 수 있다는 장점을 갖는다.

 

실행한 결과는 다음과 같다. 의도한대로 sheet3에서는 3개의 데이터만 입력된 것을 확인할 수 있다. 

 

예제 (3) 컬렉션 이용하기

 

이번에는 배열 대신에 컬렉션 객체를 이용하여 같은 작업을 처리해보자.

다만 데이터를 입력할 때, Sheet3의 A1:A2까지만 입력되도록 지정해보자. Sheet1, Sheet2는 이전 예제와 동일하다.

 

Sub 단추4_Click()
  '콤보박스를 제어하기 위한 ControlFormat을 cf라는 변수에 할당
  Set cf = ActiveSheet.Shapes("드롭다운 1").ControlFormat
  '콤보박스 초기화 실행
  cf.RemoveAllItems
 'Sheet1, Sheet2, Sheet3을 지시하기 위한 변수를 선언하고 값 할당
  Dim sh1 As Worksheet
  Dim sh2 As Worksheet
  Dim sh3 As Worksheet
  Set sh1 = Worksheets("sheet1")
  Set sh2 = Worksheets("sheet2")
  Set sh3 = Worksheets("sheet3")
  '컬렉션 객체 선언
  Dim rngCollection As New Collection
  '컬렉션 객체에 Sheet1, Sheet2, Sheet3의 데이터 영역 입력
  rngCollection.Add Item:=sh1.Range("A1:A4")
  rngCollection.Add Item:=sh2.Range("A1:A4")
  rngCollection.Add Item:=sh3.Range("A1:A2")
  '컬렉션 및 Range를 순회하며 콤보박스에 값 입력
  For Each Rng In rngCollection
    For Each Item In Rng.Cells
        cf.AddItem Item.Value
    Next Item
  Next Rng
  
 
End Sub

 

위의 코드도 (1)번 예제와 유사한 작업을 수행하지만,  중첩 For문으로 데이터 입력을 처리할 수 있다는 장점을 갖는다.

 

실행한 결과는 다음과 같다. 의도한대로 sheet3에서는 2개의 데이터만 입력된 것을 확인할 수 있다.

 

 

이번 포스트에서는 여러 시트에 있는 데이터를 콤보박스에 넣는 방법에 대해서 다뤘다. 컬렉션 객체에 대해서는 VBA 카테고리에서 한 번 더 자세히 다룰 예정이다.