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

전체 목차

 - 001. 특정 행을 복사하여 삽입하기

 - 002. 선택하여 붙여 넣기 기능 정리

 - 003. 변수/Assignment/Loop/비교

 - 004. 데이터가 있는 셀의 범위 알아내기

 - 005. 데이터 시트(쉘) 순회하기

 - 006. 셀 병합 하기

 - 007. 변수에 저장되어 있는 데이터 확인하기 (디버그 기능) 

 - 008. 정렬하기 (Sort)

 - 009. 버튼 컨트롤 (Excel Form vs. ActiveX)

 

포스트 목차

01. 콤보 상자 컨트롤 삽입 (Excel From(양식 컨트롤) vs ActiveX 컨트롤)

02. 컨트롤 속성 및 설정 변경

03. 이벤트 처리

04. VBA에서 접근하기

05. 간단한 예제

 

본 포스트에서는 엑셀에서 제공하는 폼 컨트롤(양식 컨트롤)과 ActiveX컨트롤의 콤보상자(콤보박스)를 엑셀 시트에 삽입해보고 둘의 차이점에 대해서 다루려고 한다.

 

01. 콤보 상자 컨트롤 삽입 (Excel From(양식 컨트롤) vs ActiveX 컨트롤)

  1) 양식 컨트롤 콤보박스 삽입

      개발도구 >> 삽입 >> 콤보상자(양식 컨트롤)을 추가

           드래그를 이용하여 추가할 수 있으며, 초기 이름은 "드롭다움" + 숫자"의 조합이다.

 

 2) ActiveX 컨트롤 콤보박스 삽입

      이번에는 ActiveX 컨트롤 콤보박스를 삽입해보자

      개발도구 >> 삽입 >> 콤보 상자(AcvtieX 컨트롤)

삽입 후 이름은 ComboBox1로 결정되는 것을 알 수 있다.

 

 

02. 컨트롤 속성 및 설정 변경

  1) 양식 컨트롤 콤보박스 서식

     디자인모드 > 콤보박스 선택 > 우클릭 >컨트롤 서식으로 서식을 설정할 수 있다.

 

크기, 배율, 위치속성을 설정할 수 있으며, 컨트롤 탭에서는 콤보박스에 들어갈 아이템들을 입력 범위로 지정하여 넣을 수 있다. 또한 셀 연결로 특정셀을 지정하면, 사용자가 선택한 아이템이 몇번째인지를 숫자로 해당 셀에 표기해준다. 목록 표시 줄 수 에서는 한번에 보여지는 아이템의 수를 나타낼 수 있다.

 

  2) ActiveX 콤보박스 서식 및 속성

     서식은 디자인모드 > 컨트롤 선택 > 우클릭 > 컨트롤 서식으로 알아볼 수 있다.

속성은 디자인모드 > 컨트롤 선택 > 우클릭 > 속성 또는 디자인모드 > 컨트롤 선택 > 상위의 속성 메뉴로 알아 볼 수 있다.

서식에서는 크기 및 회전, 배율, 개체 위치에 대한 속성을 변경할 수 있다.

속성에서는 폰트, 백그라운드 컬러 등 다양한 내용을 수정할 수 있다.

 

 

03. 이벤트 처리

 콤보박스를 사용하는 이유는 사용자가 콤보박스에 들어있는 여러 내용 중 하나를 선택 했을 때, 특정 동작을 수행하기 위함이다.  

 

 1) 양식 컨트롤 콤보박스 매크로

  양식 컨트롤 콤보박스의 경우 사용자가 특정값을 선택했을 때의 처리를 매크로로 지정할 수 있다. 

  디자인모드 > 컨트롤 선택 > 우클릭 > 매크로 지정을 클릭하여 지정이 가능하며, 모듈 부분에 Sub 프로시저가 생성된다.

 

  2) ActiveX 콤보박스 코드 보기

   ActiveX 컨트롤 콤보박스의 경우는 디자인모드 > 컨트롤 선택 > 우클릭 > 코드보기로 이벤트 처리 루틴을 추가 할 수 있으며 Sheet 부분에 sub 프로시저가 생성된다.

     ActiveX의 경우 콤보박스의 값이 바뀌는 동작(Change)  외에도 다양한 이벤트에 대한 처리를 제공한다. VB 에디터에서 제공하는 콤보박스로 선택할 수 있다.

 

04. VBA에서 접근하기

 

1) 양식 컨트롤 콤보박스

 - 모듈에 있는 Sub 프로시저 또는 Sheet에 있는 Sub 프로시저에서의 접근

  엑셀 폼 컨트롤은 Shaspe 객체로 볼수 있기 때문에 다음과 같이 접근이 가능하다.

  (ActiveSheet 대신에 WorkSheet를 특정할 수 있는 객체면 사용가능하다)

  ActiveSheet.Shapes("컨트롤 이름")

  아이템을 추가하려면 다음과 같은 코드를 사용하면 된다.

  ActiveSheet.Shapes("드롭다운 3").ControlFormat.AddItem "내용"

 

  *콤보박스에 관련되어 주로 관심있게 봐야하는것은 Shapes.ControlFormat이다. 

https://docs.microsoft.com/en-us/office/vba/api/excel.controlformat   

ControlFormat object (Excel)

 

2) ActiveX 콤보박스

 - Sheet 에 있는 Sub 프로시저에서의 접근

   콘트롤 이름으로 바로 접근가능하다.   

   예를 들어 다음의 코드는 콤보박스에 있는 내용을 전부 삭제하는 것이다.

   ComboBox1.Clear

 

 - 모듈에 있는 Sub 프로시저에서의 접근

  ActiveX 컨트롤은 OLE객체이기 때문에 다음과 같이 접근이 가능하다. (편의상 ActiveSheet으로 워크시트 부분을 사용한다. 다른 시트 객체를 써도 상관없다.)

  ActiveSheet.OLEObjects("컨트롤이름").Object

  예를 들어 다음의 코드는 콤보박스에 "테스트"라는 내용을 추가하는 것이다.

  ActiveSheet.OLEObjects("ComboBox2").Object.AddItem "테스트"

   

 

05. 간단한 예제

 

엑셀 양식 컨트롤(콤보박스) 2개와 ActiveX 콤보박스 2개를 삽입한다.

 - 양식 콤보박스1은 컨트롤 서식을 이용하여 입력 범위와 셀 연결을 지정 한다

  F2:F7까지의 데이터가 콤보박스에 들어가며, 선택한 아이템의 인덱스가 G2에 기록된다.

- 양식 콤보박스2와 ActiveX 콤보박스1은 해당 워크시트가 활성화되면 기존에 있었던 내용을 삭제하고 F2:F7에 있는 아이템들을 콤보박스에 입력 받는다.

- 양식 콤보박스1에서 아이템 선택 시에는 아이템 인덱스가 메시지 박스를 통해 표기된다.

- 양식 콤보박스2 에서 아이템 선택 시에는 아이템 인덱스가 G3에 표기된다.

- ActiveX 콤보박스1에서 아이템 선택 시에는 아이템 인덱스가 G4에 표기된다.

- Sub 프로시저  activexComboinsert 를 실행하면 ActiveX 콤보박스2에 "테스트"라는 항목이 추가된다.

 

코드는 다음과 같다.

 

 - Sheet1에 정의된 Sub 프로시저

'시트1에 귀속되어 있는 Sub 프로시저
'ActiveX 콤보박스 컨트롤1의 값이 변경되었을 때 실행
Private Sub ComboBox1_Change()
 
    '변경된 인덱스를 셀G3 에 표시한다.
    'ActiveX 콤보박스의 ListIndex는 0부터 시작한다는 것에 주의하자
    ActiveSheet.Cells(3, 7).Value = ComboBox1.ListIndex + 1
    
End Sub
 
'워크시트가 활성화 되었을 때 실행
Private Sub Worksheet_Activate()
    'ActiveX 콤보박스의 아이템을 모두 제거한다.
    ComboBox1.Clear
    'cf 라는 변수에 드롭다운 3 할당
    '(여기서는 2번째로 추가한 양식 컨트롤 콤보박스를 의미한다.
    Set cf = ActiveSheet.Shapes("드롭다운 3").ControlFormat
    '양식 컨트롤 콤보박스의 모든 데이터를 제거한다.
    cf.RemoveAllItems
    'F2~F7에 입력되어있는 데이터를 ActiveX 컨트롤 콤보박스1과 양식 컨트롤 콤보박스2에 삽입한다.
    For RowIndex = 2 To 7
        ComboBox1.AddItem ActiveSheet.Cells(RowIndex, 6).Value
        cf.AddItem ActiveSheet.Cells(RowIndex, 6).Value
    Next RowIndex
 
End Sub

 

- 모듈에 정의된 Sub 프로시저

 

'양식 컨트롤 콤보박스1의 내용이 변경되었을 경우 호출
Sub 드롭다운1_Change()
    Set cf = ActiveSheet.Shapes("드롭다운 1").ControlFormat
    '메시지 박스에 선택된 인덱스 출력 (여기서의 인덱스는 1부터 시작)
    MsgBox (cf.List(cf.ListIndex))
End Sub
 
'양식 컨트롤 콤보박스2의 내용이 변경되었을 경우 호출
Sub 드롭다운3_Change()
    Set cf = ActiveSheet.Shapes("드롭다운 3").ControlFormat
    '셀 G4에 선택된 인덱스 출력
    ActiveSheet.Cells(4, 7).Value = cf.ListIndex
End Sub
 
Sub activexComboinsert()
    'ActiveX 컨트롤 콤보박스2에 테스트라는 아이템 추가
    ActiveSheet.OLEObjects("ComboBox2").Object.AddItem "테스트"
End Sub

 

매크로 activexComboInsert를 두번 실행하면, ActiveX 콤보박스2에 테스트가 두번 들어가있는 것을 볼 수 있다.

 

 

ActiveX 컨트롤의 경우와 엑셀 양식 컨트롤 (엑셀 폼 컨트롤)의 경우 접근하는 방법/객체에 차이가 있다는것을 숙지하고 넘어가자