Excel [지식인 해결 시리즈] 004. 목록상자, 토글버튼 연동

 

전체 목차

001. lookup 함수를 이용한 양도세 구간 구하기

002. 공백 셀 제거 후 정렬

003.데이터 분석 후 행 추가하기 (나누기, 나머지, 정수)

004. 목록상자, 토글버튼 연동

005. 다른 시트에 있는 특정 데이터를 복사해오기

006. 근무시간에서 특정시간 구하기 (야간 근무 인정 시간)

 

포스트 목차.

01. 문제

02. 문제해결 방법

03. 예제 및 설명

 

 

01. 문제

 

문제를 요약하면, 목록상자의 아이템과 토글버튼들의 상태를 연동하자는 이야기로 보입니다. 

  - 목록상자의 아이템을 클릭하면, 해당 아이템에 연동된 값으로 토글버튼의 값을 업데이트 한다.

  - 목록상자의 다른 아이템을 클릭하면, 현재 토글버튼의 값들을 이전 아이템에 저장하고, 새로 선택한 아이템에 연동된 값으로 토글버튼의 값을 업데이트 한다.

 

02. 문제해결 방법

1) 목록상자와 토글버튼을 엑셀 시트에 추가

2) 목록상자를 클릭 했을 때, 처리를 위한 루틴 

3) 목록상자에서 선택된 아이템 번호 저장 

4) 목록상자 아이템별로 토글버튼의 상태를 저장 

5) 저장된 토글버튼의 상태 정보를 기반으로 토글버튼 업데이트 

 

- 목록상자와 토글버튼을 엑셀 시트에 추가

개발도구 > 삽입 > ActiveX 컨트롤 > 목록상자를 선택한다. (드래그로 삽입가능)

개발도구 > 삽입 > ActiveX 컨트롤 > 토글단추

질문자의 요청대로 토글단추는 5개 만들어준다. 또한 상태를 초기화하는 initialize 버튼(명령단추)도 하나 추가해 준다. 

 

목록상자에 Sheet1~Sheet5의 값을 넣어주고, 토글 버튼의 이름을 1~5로 변경해준다.  여러가지 방법이 있지만 본 포스트에서는 다음과 같은 매크로를 작성했다. 

 

Sub Init()
 
    Set btn1 = ActiveSheet.OLEObjects("ToggleButton1").Object
    Set btn2 = ActiveSheet.OLEObjects("ToggleButton2").Object
    Set btn3 = ActiveSheet.OLEObjects("ToggleButton3").Object
    Set btn4 = ActiveSheet.OLEObjects("ToggleButton4").Object
    Set btn5 = ActiveSheet.OLEObjects("ToggleButton5").Object
    Set ListBox1 = ActiveSheet.OLEObjects("listBox1").Object
    
   
    btn1.Caption = "1"
    btn2.Caption = "2"
    btn3.Caption = "3"
    btn4.Caption = "4"
    btn5.Caption = "5"
    ListBox1.Clear
    ListBox1.ListIndex = -1
    ListBox1.AddItem "Sheet1"
    ListBox1.AddItem "Sheet2"
    ListBox1.AddItem "Sheet3"
    ListBox1.AddItem "Sheet4"
    ListBox1.AddItem "Sheet5"
 
End Sub
 

위 코드를 설명하기 전에 일단 각각의 컨트롤(목록상자, 토글 버튼, 명령버튼)에 접근할 수 있는 이름을 알아야한다. 

위의 그림처럼 디자인 모드로 들어간 후에 컨트롤을 선택하고 이름상자에서 확인이 가능하다.

 

위의 Sub 프로시저 Init은는 모듈에 정의되어 있고, 컨트롤들은 WorkSheet에 포함되어 있기 때문에 Sub 프로시저에서 바로 컨트롤에 접근하는 것은 불가능하다.

 

Set ListBox1 = ActiveSheet.OLEObjects("listBox1").Object

 

위와 같은 방식으로 OLEObjects("이름").Object를 변수에 할당하여 접근하도록 하자.

 

토글버튼에서 우리에게 보여지는 텍스트부분은 Caption 속성을 변경하여 지정할 수 있다.

Set btn1 = ActiveSheet.OLEObjects("ToggleButton1").Object

btn1.Caption="1"

 

또는

ActiveSheet.OLEObjects("ToggleButton1").Object.Caption="1"

 

목록상자의 내용을 비우고, 선택된 아이템이 없게 마드려면 다음 커맨드를 이용하면 된다.

Set ListBox1 = ActiveSheet.OLEObjects("listBox1").Object

ListBox1.Clear

ListBox1.ListIndex = -1

 

또는

ActiveSheet.OLEObjects("listBox1").Object.Clear

ActiveSheet.OLEObjects("listBox1").Object.ListIndex = -1

 

ListIndex 는 현재 선택되어 있는 아이템의 순서를 뜻한다.

 

목록상자에 아이템을 입력할 때는 AddItem을 사용한다.

Set ListBox1 = ActiveSheet.OLEObjects("listBox1").Object

ListBox1.AddItem "Sheet1"

 

또는

 

ActiveSheet.OLEObjects("listBox1").Object.AddItem "Sheet1"

 

실행 전

실행

실행 후

 

- 목록상자를 클릭 했을 때, 처리를 위한 루틴 

 

위 그림처럼 디자인모드에서 목록상자를 우 클릭 한 후에 코드 보기를 선택한다. 

 

 

위 그림처럼 Sheet1 객체에 LisbBox1_Click() Sub 프로시저가 만들어지는 것을 확인할 수 있다.

위 Sub 프로시저에서는 Sheet1에 있는 콘트롤에 대한 접근이 직접적으로 가능하다.  이 부분을 수정하면 ListBox1이 클릭될 때마다 특정 루틴을 실행할 수 있게 된다. 

 

- 목록상자에서 선택된 아이템 번호 저장 

목록상자에서 선택된 아이템 번호를 저장하는 변수가 필요하다. 현재 선택되어 있는 아이템 번호는 ListIndex 속성을 통해 알 수 있지만, 우리는 이전에 선택되어 있던 아이템 번호도 알아야 되기 때문에 전역 변수를 하나 선언한다.

 

Sub 프로시저 밖에 다음과 같이 변수를 하나 선언한다.

Dim selectedIndex As Integer

 

- 목록상자 아이템별로 토글버튼의 상태를 저장 

현재 총 5개의 목록상자 아이템이 있고, 목록상자 아이템마다 5개의 버튼의 상태가 있다. 토글 스위치는 눌려 있는 상태와 눌려있지 않은 상태로 구분할 수 있다.

 

ToggleButton1.Value 의 값이 True이면 눌려있는 상태

ToggleButton1.Value 의 값이 False이면 보통상태

총 25개의 상태를 기억해야 한다.  Boolean 변수 25개를 선언해서 처리할 수도 있지만 이번에는 2차원 배열을 만들어서 저장해보자. 

 

다음과 같은 전역 배열을 선언한다.

Dim sheetState(1 To 5, 1 To 5)  As Boolean

sheetState( 아이템순서, 버튼순서) 로 접근하여 토글 버튼의 상태를 저장하려고 한다.

 *배열은 다음 포스트에서 좀 더 다룰 예정이다.

 

- 저장된 토글버튼의 상태 정보를 기반으로 토글버튼 업데이트 

 

시트 번호를 파라미터로 입력받고, 토글 버튼의 상태를 변경해주는 Sub 프로시저를 만들어보자.

Private Sub redrawButtons(sheetIndex)
    ToggleButton1.Value = sheetState(sheetIndex, 1)
    ToggleButton2.Value = sheetState(sheetIndex, 2)
    ToggleButton3.Value = sheetState(sheetIndex, 3)
    ToggleButton4.Value = sheetState(sheetIndex, 4)
    ToggleButton5.Value = sheetState(sheetIndex, 5)
End Sub

위의 Sub 프로시저가 호출되면 토글 버튼의 상태가 sheetState 배열에 저장되어 있는 상태로 변경된다.

 

- 초기화 동작 구현

처음에 명령버튼을 하나 추가했는데, 그 버튼을 누르면 시트 상태가 초기화 되는 루틴을 추가해보자

 1) 모든 토글 스위치의 상태가 False가 되고,

 2) 목록상자의 아이템이 미선택된 상태로 만들고,

 3) sheetState 배열의 모든 값을 False로 바꾼다.

 

위와 같은 방식으로 코드를 추가한다.

 

Private Sub CommandButton1_Click()
    For i = 1 To 5
        For j = 1 To 5
            sheetState(i, j) = False
        Next j
    Next i
    ListBox1.ListIndex = -1
    selectedIndex = -1
    ToggleButton1.Value = False
    ToggleButton2.Value = False
    ToggleButton3.Value = False
    ToggleButton4.Value = False
    ToggleButton5.Value = False       
    
End Sub

For 루프 두번을 돌면서 sheetState 배열의 모든 값을 False로 만든다.

ListBox1.ListIndex에 -1을 할당하여 미선택 상태로 만든다.

전역변수 selectedIndex 의 값에 -1을 할당한다.

모든 토글 버튼을 False 상태로 만든다.

 

- 다른 Sub 프로시저 실행

파라미터가 없는 경우) Call 서브프로시저_이름 

파라미터가 있는 경우) Call 서브프로시저_이름(파라미터,....)

*지금 자세히 설명하기에는 이야기가 길어질 것 같아서 이 내용도 다음 포스트에서 정리해보겠습니다.

 

03. 예제 및 설명

 

프로시저의 흐름

1) 목록 상자가 클릭되는 순간, 이전에 선택된 아이템 번호(전역 변수 selectedIndex 이용)와 토글버튼의 상태를 sheetState 배열에 저장한다.

2) 현재 선택된 아이템 번호를 전역변수 selectedIndex에 저장한다.

3) 현재 선택된 아이템 번호를 이용하여 sheetState 배열에서 버튼들의 상태를 가져온다.

4) 가져온 버튼의 상태대로 토글 버튼들의 상태를 바꾼다.

 

위의 흐름에 기반하여 작성한 코드는 다음과 같다.

 

 

'토글 버튼의 상태를 저장하기 위한 5X5 배열 변수 선언
Dim sheetState(1 To 5, 1 To 5)  As Boolean
'선택되어있는 목록 박스의 인덱스 저장용 변수
Dim selectedIndex As Integer
 
'초기화 Sub 프로시저 (initialize 버큰 클릭 시)
Private Sub CommandButton1_Click()
    '다중 루프를 도며 sheetState 배열의 값을 False로 초기화
    For i = 1 To 5
        For j = 1 To 5
            sheetState(i, j) = False
        Next j
    Next i
    '목록 박스를 미선택 상태로 변경
    ListBox1.ListIndex = -1
    '전역변수 selectedIndex 초기화
    selectedIndex = -1
    '모든 토글 버튼의 산태를 False로 변경
    ToggleButton1.Value = False
    ToggleButton2.Value = False
    ToggleButton3.Value = False
    ToggleButton4.Value = False
    ToggleButton5.Value = False        
    
End Sub
 
'버튼의 상태를 저장하는 서브루틴
' 파라미터1(sheetIndex): 아이템 박스의 인덱스
' 파라미터2(buttonIndex): 버튼의 인덱스 
' 파라미터3(buttonValue): 버튼의 상태값
Private Sub updateSheetState(sheetIndex, buttonIndex, buttonValue)
    '인덱스가 유효한 값에 대해서만 sheetState 배열의 값을 변경한다.
    If sheetIndex >= 1 And sheetIndex <= 5 Then
        sheetState(sheetIndex, buttonIndex) = buttonValue
    End If
End Sub
 
'버튼의 상태를 업데이트하는 루틴
' 파라미터1(sheetIndex): 아이템 박스의 인덱스
Private Sub redrawButtons(sheetIndex)
    '전역 변수 배열인 sheetState의 선택된 아이템박스 인덱스에 해당하는 버튼의 상태값을 가져와 버튼을 업데이트한다.
    ToggleButton1.Value = sheetState(sheetIndex, 1)
    ToggleButton2.Value = sheetState(sheetIndex, 2)
    ToggleButton3.Value = sheetState(sheetIndex, 3)
    ToggleButton4.Value = sheetState(sheetIndex, 4)
    ToggleButton5.Value = sheetState(sheetIndex, 5)
End Sub
 
'목록 상자 클릭 시 처리할 루틴
Private Sub listBox1_Click()
    'sheetIndex라는 로컬 변수에 클릭된 인덱스 값 저장 (1을 더하는 이유는 ListIndex는 0부터 시작하기 때문)    
    sheetIndex = ListBox1.ListIndex + 1
    '버튼의 상태를 저장하는 서브 루틴 호출 
    '아직 selectedIdnex라는 전역 변수에는 이전 선택된 인덱스의 값이 저장되어 있다.
    Call updateSheetState(selectedIndex, 1, ToggleButton1.Value)
    Call updateSheetState(selectedIndex, 2, ToggleButton2.Value)
    Call updateSheetState(selectedIndex, 3, ToggleButton3.Value)
    Call updateSheetState(selectedIndex, 4, ToggleButton4.Value)
    Call updateSheetState(selectedIndex, 5, ToggleButton5.Value)
    '전역변수에 클릭된 인덱스 값 저장
    selectedIndex = sheetIndex
    '버튼의 상태를 변경해주는 서브 루틴 호출
    redrawButtons (sheetIndex) 
End Sub
 
 
 
 

 

이제 목록 상자와, 토글버튼을 바꾸면서 동작을 해보자.

 

Sheet1을 선택 후 토글 버튼 1, 3, 5를 클릭해보자.

Sheet2를 선택하면 토글 버튼 12345 가 False 상태일 것이다.

다시 Sheet1을 성택했을 때 위에서 저장한 1, 3, 5의 상태가 토글된 것을 확인할 수 있다.