안녕하세요 모닝수잔입니다 :)
회사 생활을 하다보면 참 많은 데이터를 다루게 되는데요, Ctrl + F를 누르지 않고도 직원/학생/고객의 정보를 바로바로 찾을 수 있다면 참 편리하겠죠? 그래서 오늘은 간단한 표를 만들어서 이름을 선택했을 때 생년월일, 주소, 핸드폰번호가 조회되는 프로세스를 비쥬얼베이직(VBA)을 활용해서 매크로로 만들어보겠습니다.
우선 간단한 표를 준비해봤습니다. 블랙핑크(BLACKPINK) 멤버들의 이름, 생년월일, 주소, 핸드폰번호를 임의로 넣어봤는데요, "사용자 정의 폼"을 사용해서 각 멤버들의 이름을 선택하면 생년월일, 주소, 핸드폰번호가 조회되는 프로세스입니다.
"사용자 정의 폼" 만들기
1) 엑셀 - 개발 도구 - Visual Basic - 삽입 - 사용자 정의 폼을 차례대로 클릭합니다.
위의 순서대로 차례대로 클릭하시면 아래 오른쪽 빨간칸처럼 UserForm1이라는 새로운 "사용자 정의 폼"이 생성됩니다. "사용자 정의 폼"은 알림창처럼 생각해주시면 되는데요, 마치 엑셀을 닫을 때 "저장하시겠습니까?"라는 메세지 팝업이 뜨는 것과 같은 창을 직접 만드는 과정이라고 생각하시면 됩니다.
사용자 정의 폼 "도구 상자"
사용자 정의 폼은 해당 폼을 예쁘게 만들어 주기 위한 "도구 상자"를 가지고 있습니다. 사용자 정의 폼을 삽입하면 자동으로 생성되는데요, 왼쪽에서 → 오른쪽 순서대로 도구 상자 안에 있는 각 기능을 간단하게 살펴보겠습니다.
※ 혹시나 도구상자가 안보이신다면, 보기 - 도구상자를 클릭해주세요 :)
▶ 개체 선택 : 화살표 모양은 개체 선택 도구로 사용자 정의 폼 안에 있는 개체를 선택할 때 사용됩니다.
▶ 레이블 : A 모양은 사용자 정의 폼 안에 텍스트를 입력할 때 사용합니다.
▶ 콤보 상자 : 드롭다운 형식의 상자로 클릭했을 때 여러가지 옵션을 보여주는 상자입니다.
▶ 목록 상자 : 여러가지 옵션이 목록으로 나타나 있어서 선택할 수 있는 상자입니다.
▶ 확인란 : 체크 모양처럼 체크 표시를 할 수 있는 체크박스입니다.
▶ 옵션 단추 : 여러가지 옵션 중 하나의 값을 선택할 수 있는 동그라미 모양 단추입니다.
▶ 토글 단추 : on 또는 off 중 하나의 상태를 갖는 단추입니다.
▶ 프레임 : 말 그대로 사용자 정의 폼 안에서 하나의 범위를 묶어주는 프레임으로 미관상의 이유로 사용됩니다.
▶ 명령 단추 : 조회, 취소 등 특정 명령을 실행하는 단추입니다.
▶ 연속 탭 : 사용자 정의 폼을 탭으로 나눠줄 때 사용합니다.
▶ 다중 페이지 : 여러 개의 페이지를 탭으로 구분하는 대화상자를 만들 때 사용합니다.
▶ 스크롤 막대 : 사용자 정의 폼에서 넓은 범위의 값을 조정할 때 사용하는 스크롤 막대입니다.
▶ 스핀 단추 : 사용자 정의 폼에서 좁은 범위의 값을 조정할 때 사용하는 스핀 단추입니다.
▶ 이미지 : 사용자 정의 폼에 그림을 표시할 때 사용합니다.
▶ RefEidt : 사용자 정의 폼에서 엑셀 시트 내 작업 범위를 선택할 때 사용합니다.
2) 사용자 정의 폼, UserForm1의 이름을 "조회폼"이라고 변경해줍니다.
사용자 정의 폼을 새롭게 삽입하면 UserForm1이라는 이름으로 자동으로 설정됩니다. 즉, 사용자 정의 폼 자체의 이름이 UserForm1이 되는데요, 앞으로 이 사용자 정의 폼을 좀 더 편하게 부르기 위해서 "조회폼"이라고 변경해보겠습니다.
단, 이때 주의하실 점이 있는데 사용자 정의 폼의 실제 이름과 보여지는 이름이 다르다는 점인데요, 아래 화면 왼쪽 빨간칸처럼 (이름)에 들어가는 부분은 이 사용자 정의 폼 자체의 실제 이름이 되고 Caption에 들어가는 부분은 이 사용자 정의 폼에 보여지는 이름(제목)이 됩니다. 예를 들어, 만약 Caption에 "조회폼"이 아니라 "이름 조회하기"라고 적었다면 오른쪽 빨간칸의 사용자 정의 폼에 있는 제목은 "조회폼"이 아니라 "이름 조회하기"가 되었겠죠.
3) 도구 상자에서 "레이블"을 선택해서 사용자 정의 폼 안에 텍스트를 적습니다.
이제부터 본격적으로 사용자 정의 폼을 꾸며보겠습니다. 우리는 "이름"을 선택했을 때 생년월일, 주소, 핸드폰번호 등의 정보를 보여주는 사용자 정의 폼을 만들 예정이기 때문에, 사용자가 사용자 정의 폼의 기능을 쉽게 인지할 수 있도록, "이름을 선택해주세요."라는 텍스트를 적어봤습니다. 왼쪽 빨간칸의 속성 부분을 보시면 (이름)에 들어간 Label1은 이 텍스트 레이블 자체의 이름이고, 보여지는 내용은 Caption에 적혀있는 "이름을 선택해주세요."라는 텍스트라는 사실을 확인하실 수 있죠. 이 텍스트는 Font 부분을 더블클릭해서 글씨체, 글씨크기 등을 변경할 수 있고 ForeColor 부분을 클릭해서 글씨색상을 변경할 수도 있습니다.
※ TextAlign : 속성에서 TextAlign을 보시면 왼쪽 정렬(Left), 가운데 정렬(Center), 오른쪽 정렬(Right)도 선택할 수 있답니다 :)
4) 도구 상자에서 레이블과 콤보 박스, 텍스트 박스를 이용해서 이름, 생년월일, 주소, 핸드폰번호를 입력합니다.
다음으로 "이름"을 선택했을 때 생년월일, 주소, 핸드폰번호 정보를 보여주기 위한 공간을 사용자 정의 폼 안에 만들어보겠습니다. 레이블을 사용해서 이름, 생년월일, 주소, 핸드폰번호라는 텍스트를 적어놓고, "이름" 옆에는 콤보 박스를 추가하고 나머지 정보 옆에는 텍스트 박스를 추가했습니다. 우리가 만들려는 프로세스가 "이름"을 선택하면 나머지 정보를 보여주는 것이기 때문에, 여러 옵션 중에서 특정 "이름"을 선택할 수 있는 콤보박스를 이름 옆에 두고, 나머지 정보는 정해진 하나의 정보만을 보여주기 때문에 텍스트 박스를 추가한 것이죠. 또 한가지 주목할만한 점은 우리가 만든 "이름" 옆에 있는 콤보박스 이름이 ComboBox1이고, 생년월일, 주소, 핸드폰번호 옆에 있는 텍스트 박스의 이름은 TextBox1, TextBox2, TextBox3이라는 점이랍니다.
5) 도구 상자에서 명령 단추를 이용해서 "조회"와 "닫기" 버튼을 만들어줍니다.
마지막으로 명령 단추를 클릭한 뒤 버튼을 만들고 "조회"와 "닫기"라고 적어주면 사용자 정의 폼이 완성됩니다. 이때, 각 명령 단추의 (이름)을 확인해보시면 "조회"는 CommandButton1, "닫기"는 CommandButton2로 자동설정되었음을 확인하실 수 있습니다. 이제 각 버튼과 박스에 명령과 보여지는 값을 부여해주면 되는데요, 지금은 아무리 그 어떤 버튼을 클릭해도 아무런 변화가 일어나지 않기 때문입니다.
※ (이름)의 확인
앞서 계속 사용자 정의 폼이나 콤보 박스, 명령 단추 등의 (이름)을 확인하고 있는데요, 앞으로 명령을 부여할 때 해당 (이름)이 필요하기 때문이랍니다. ComboBox1, CommandButton1 ... 의 이름이 혼동되신다면 UserForm1을 "조회폼"으로 변경했던 것처럼 기억하기 편한 이름으로 변경하셔도 됩니다 :)
"사용자 정의 폼" 활용하기
이제 본격적으로 Module창을 열고 VBA를 활용해 매크로를 만들어보겠습니다.
1) 모듈 창을 열고 Sub 프로시저를 열어줍니다.
VBA의 기본은 "열고 닫기"이니까요, 삽입 - 모듈을 클릭해서 새로운 모듈을 열고 sub 프로시저를 열어줍니다. 저는 sub 프로시저의 이름을 간단하게 "프로시져"라고 만들어봤는데요, 모듈 창에 sub 프로시져()라고 치고 엔터를 눌러주시면 되겠죠?
2) Sub 프로시저 안에 "조회폼.Show"를 입력합니다.
우리가 앞서 만든 사용자 정의 폼의 이름을 "조회폼"이라고 정했었습니다. 조회폼.show라는 명령을 적어서 Sub 프로시저를 실행했을 때 조회폼이 나타나도록 만들어줬습니다. 이제 이 프로시져는 조회폼을 나타내주는 매크로가 됩니다.
3) 개발 도구 - 삽입에서 단추(양식 컨트롤)를 추가해서 앞에서 만든 프로시져를 매크로로 지정합니다.
프로시져 매크로를 만들었으니 이를 실행할 트리거(Trigger)값을 만들어줘야하는데요, 엑셀 시트로 돌아와서 개발 도구 - 삽입에 있는 양식 컨트롤에서 단추모양 아이콘을 클릭하고 "조회폼"이라는 단추를 새로 하나 만들어줍니다. 이때, "매크로 지정"이라는 새로운 창이 뜰텐데, 앞서 만든 프로시져를 선택했습니다. 이제 단추모양 아이콘을 클릭하면 조회폼이 자동으로 생성될 겁니다.
4) VBA창으로 돌아와서 조회폼을 더블클릭하고 상단 메뉴에서 UserForm - Initialize를 선택합니다.
이제 단추를 클릭하면 조회폼이 실행되도록 만들었으니, 해당 조회폼 안에 있는 내용들에게 명령을 추가해보겠습니다. 먼저 조회폼이 처음 실행되었을 때 우리는 "이름" 중에 하나를 콤보박스에서 선택할 것이기 때문에 사용자 정의 폼(UserForm)이 처음 실행되었을 때(Initialize) 콤보박스 안에 "이름"들이 나타나도록 설정해주겠습니다.
아래 화면 빨간칸과 같이 상단 메뉴에서 UserForm - Initialize를 선택하면 Private Sub UserForm_Initialze()가 자동으로 생성되는데요, ComboBox1.RowSource = "B3:B6"이라고 입력해줍니다. "사용자 정의 폼" 만들기 4)에서 우리는 콤보박스 이름이 ComboBox1이라는 사실을 확인했었습니다. 즉, ComboBox1에서 RowSource(각 행에 있는 정보)를 보여줄건데, 그 범위가 B3에서 B6셀에 있는 "이름"들이라는 명령입니다.
5) 상단메뉴에서 CommandButton1 - Click를 선택합니다.
4)까지 실행하셨다면 사용자 정의 폼에서 콤보박스를 클릭하면 김지수, 김제니, 박로제, 라리사라고 "이름"들이 생성되었음을 확인하실 수 있습니다. 이제는 콤보박스에서 "이름"을 선택하고 "조회"를 클릭했을 때, 해당 "이름"의 정보들을 텍스트박스에서 보여주도록 만들어야 하는데요, 앞서 텍스트박스의 이름은 TextBox1(생년월일), TextBox2(주소), TextBox3(핸드폰번호)이었죠, 저는 좀 더 편리하게 기억하기 위해 모두 소문자 textbox1, textbox2, textbox3으로 변경했습니다.
"조회" 버튼의 이름은 CommandButton1이므로 상단메뉴에서 CommanButton1 - Click을 선택하면 CommandButton1_Click()가 자동으로 생성됩니다. 사용자가 "이름"을 선택한 뒤 "조회" 버튼을 클릭했을 때 어떤 명령을 부여할지 입력해주면 되는데요, 여기에서 사용자가 콤보박스에서 어떤 "이름"을 선택할지 모르기 때문에 변수를 생성해야하는데 변수 이름을 a라고 하겠습니다. a = ComboBox1.ListIndex + 3 이라고 입력해줍니다.
왜일까요?
예를 들어, 사용자가 "이름" 콤보박스에서 첫번째에 있는 "김지수"를 선택했다고 합시다. 김지수는 B3 셀에 있기 때문에 textbox1(생년월일)은 C3셀의 정보를, textbox2(주소)는 D3셀의 정보를, textbox3(핸드폰번호)는 E3셀의 정보를 보여줘야 합니다. 그렇다면 textbox1.Text = Cells(3,3), textbox2.Text = Cells(3,4), textbox3.Text = Cells(3,5)라고 표현할 수 있는데요, textbox1의 텍스트는 3행 3열의 값을 보여주고, textbox2의 텍스트는 3행 4열의 값을 보여주고, textbox3의 텍스트는 3행 5열의 값을 보여달라는 뜻입니다. 여기에서 반복되는 값이 바로 3행이죠, "김지수"는" 이름" 콤보박스의 가장 맨 위에 있는 첫번째 값이지만,실제로는 3번째 값이 됩니다. 코딩을 조금이라도 아시는분이라면 리스트(List)의 가장 첫번째 값은 1이 아닌 0이라는 사실을 알고 계실겁니다. 그래서 a = ComboBox1.ListIndex + 3 이라고하면, ComboBox1의 가장 첫번째 값은 ListIndex(리스트의 순서) 0인데, 여기에 3을 더하면 "김지수"가 첫번째 값이 됩니다.여기에서앞서만든 textbox1.Text = Cells(3,3), textbox2.Text = Cells(3,4), textbox3.Text = Cells(3,5)식에서 3을 a로 대체하면, 사용자가 어떤 "이름"을 선택하더라도 해당 행에 있는 정확한 정보들을 불러올 수 있겠죠?
6) 상단메뉴에서 CommandButton2 - Click를 선택합니다.
마지막으로 상단 메뉴에서 CommandButton2 - Click을 선택하면 CommandButton2_Click()이 자동으로 생성됩니다. CommandButton2는 바로 "닫기" 버튼인데요, "닫기" 버튼을 클릭했을 때 "조회폼"이 사라져야 하므로 이때는 Unload Me라고 적어주시면 된답니다. 이제 모든 프로세스가 완성되었습니다.
최종적으로 엑셀시트에서 "조회폼"을 클릭하면 사용자 정의 폼이 생기고 "이름" 콤보박스에서 원하는 이름을 선택한 뒤 "조회"버튼을 클릭하면 생년월일, 주소, 핸드폰번호의 정보가 자동으로 텍스트박스에서 보여지게 됩니다.
오늘 다뤄본 내용은 생각보다 단순해보이지만 처음해보시는 분들에게는 생소할 수도 있는 부분인데요, 지금은 샘플양식이라서 4명의 데이터만 있었지만 400명, 4,000명의 데이터를 다루시는 분들이라면 사용자 정의 폼을 활용해서 정보를 한번에 조회하는게 업무 효율성을 높이는데 도움이 되실 수도 있답니다. 그럼 오늘도 좋은 하루 되세요 :)
