엑셀 콤보박스(ActiveX컨트롤)에 리스트 입력방법

양식 컨트롤 콤보박스와 ActiveX컨트롤 콤보박스 중 Active 컨트롤 콤보박스에 리스트(목록)을 입력하는 방법에는 몇가지가 있습니다. 

☞ 콤보박스에 리스트 입력방법

1. ListFillRange 에 범위 입력

• 셀주소로 리스트 범위 입력 : 리스트와 콤보박스가 같은 시트에 있는 경우 가능
• 정의된 이름으로 리스트 범위 입력 : 리스트와 콤보박스가 같거나 다른 시트에 있는 경우 가능

2. VBA 활용

• ComboBox.AddItem
• ComboBox.ListFillRange
• ComboBox.List

위 방법중에서 VBA를 활용하여 ComboBox(콤보박스)에 목록을 입력하는 방법을 설명드립니다.

<워크시트에서 콤보상자는 [개발도구] - [삽입] - [콤보상자]를 선택하여 삽입합니다.>

VBA를 활용하여 콤보상자에 리스트(목록) 입력하기

앞에서도 설명했지만 VBA를 활용하여 콤보박스에 리스트(목록)를 입력하는 방법은 아래와 같습니다.

1. ComboBox.AddItem 매소드

2. ComboBox.ListFillRange 속성

3. ComboBox.List 속성

이 중에서는 워크시트에 목록없이 리스트를 만들 수 있지만 사용의 편의를 위해 아래와 같이 리스트목록을 따로 만들어 사용하는 경우가 대부분입니다.

 

AddItem 메소드

Sub ComboBox리스트1()

    With Sheet1.ComboBox1

        If .ListCount >= 0 Then .Clear  '---①

        .AddItem "수입"

        .AddItem "식비"

        .AddItem "주거비"

    End With

    

End Sub

1. ① 은 콤보박스에 리스트(목록)이 있는 경우 목록을 지워 줍니다. 특시 워크시트에서 콤보박스를 사용하는 경우는 매크로가 콤보박스의 드롭버튼을 클릭했을 때 실행되도록 하는 경우가 많아 이때는 반드시 콤보박스의 목록을 지워 주어야 합니다.

2. AddItem 매소드로 콤보박스에 리스트(목록)을 하나씩 입력 합니다.

3. AddItem을 쓰는 경우는 리스트가 거의 변함이 없고 갯수가 적은 경우 사용하면 좋습니다. 

4. 워크시트에 리스트(목록)이 없어도 직접 입력이 가능합니다.

Sub ComboBox리스트2()

    

    For Each c In Sheet2.Range("A2:A7")

        Sheet1.ComboBox1.AddItem c.Value

    Next c

End Sub

1. 위 예는 워크시트에 참조할 리스트(목록)이 있는 경우 각 셀의 값을 리스트로 불러오는 방법입니다.

2. For Each 구문으로 Sheet2의 A2:A7 셀 범위의 값을 하나씩 리스트에 추가합니다.

3. 리스트 갯수가 많거나 참조하는 셀주소를 변수로 입력하여 동적인 리스트를 만들 수 있습니다.

ListFillRange 속성

Sub ComboBox리스트3()

    Sheet1.ComboBox1.ListFillRange = "주거비"

    Sheet1.ComboBox1.RowSource = "주거비"  '---① 콤보상자(ActiveX)에서 사용시 오류

    

End Sub

1. 콤보박스(ActiveX)의 속성인 ListFillRange에 참조 범위를 입력하여 리스트를 추가합니다.

2. 여기서 "주거비"는 이름정의를 사용하여 미리 정의 된 이름입니다.

3. 콤보박스와 리스트가 같은 워크시트에 있는 경우는 셀주소를 그냥 입력해도 됩니다.(예 D2:D8)

4. 참고로 Userform의 콤보박스는 ListFillRange 가 아닌 Rowsource 속성을 사용합니다.

List 속성

Sub ComboBox리스트4()

    Sheet1.ComboBox1.List = Sheet2.Range("B2:B9")  '---① 런타임 오류발생

    Sheet1.ComboBox1.List = Application.WorksheetFunction. _

                                      Transpose(Sheet2.Range("B2:B9"))

End Sub

1. List 속성은 AddItem처럼 목록을 하나씩 추가하는 방식이 아니라 배열값을 한번에 리스트(목록)에 입력합니다.

2. 그리고 ① 과 같이 범위를 입력하는 경우에는 아래와 같이 런타임 오류가 발생합니다. 배열값이 아니기 때문입니다.

3. 위와 같은 오류를 없애기 위해 워크시트 함수 Transpose를 사용합니다. Transepose 함수는 행과 열을 바꾸는 함수이지만 함수 자체가 배열함수로 결과는 배열로 반환합니다. 

Sub ComboBox리스트5()

Dim 계정 As Variant

    계정 = Array("미용비", "의복비", "잡화비", "의료비", "보건비")  '---①

    Sheet1.ComboBox1.List = 계정

    Sheet1.ComboBox1.List = Array("미용비", "의복비", "잡화비", "의료비", "보건비")  '---②

    

End Sub

1. List 속성은 배열값으로 입력되어야 합니다. 그래서 Array 함수를 이용해서 각 품목을 배열로 반환한 값을 대입하면 됩니다.

2. 다시 설명드리지만 AddItem은 하나씩 추가하고 List는 배열값을 한꺼번에 입력합니다.

3. ② 와 같이 한줄로 입력해도 무관합니다. 다만 Array 함수의 배열변수는 Variant 형으로 선언해야 된다는 것을 보여 주기 위해 ① 과 같이 코딩 해보았습니다.

Sub ComboBox리스트6()

Dim 계정(3) As String  '---배열변수는 (괄호)로 선언하고 (괄호)안 숫자는 배열크기를 나타냄.

Dim k As Byte

    

    For Each c In Sheet2.Range("F2:F5")

        계정(k) = c.Value

        k = k + 1

    Next c

        

    Sheet1.ComboBox1.List = 계정

    

End Sub

1. 위 예제는 정적배열변수를 사용한 예입니다.

2. 배열변수를 사용해서 참조범위의 각각의 값을 For Each 구문으로 배열변수에 대입하고 배열변수를 List 속성에 적용하는 방법입니다.

Sub ComboBox리스트7()

Dim 계정() As String  '동적배열선언

Dim i As Integer

i = Sheet2.Range("G1").End(xlDown).Row

ReDim 계정(1 To i - 1)  '---배열크기변경

    k = 1

    For Each c In Sheet2.Range("G2:G" & i)

        계정(k) = c.Value

        k = k + 1

    Next c

        

    Sheet1.ComboBox1.List = 계정

    

End Sub

1. 위 예제는 동적배열변수를 사용하여 리스트의 갯수가 추가되거나 줄어들어도 코드를 변경하지 않고 사용할 수 있는 예입니다. 배열에 대한 자세한 설명은 관련글 또는 TAG에서 배열을 참조 바랍니다.

관련글 보기

배열 이해하기

동적배열변수 선언

03

첨부파일 보기

콤보박스 리스트 입력방법.xlsm