VBA 사용자 정의 폼과 컨트롤 도구로 엑셀 시트에 데이터 입력하기

지금까지 VBA 작성을 위한 ActiveX 컨트롤을 사용하는 방법에 대해 알아보았습니다. 이번에는 직접 디자인한 창을 띄우고 데이터를 입력하면 엑셀 시트에 정리되어 입력되게 하는 방법에 대해 알아보고자 합니다.

 

엑셀이나 여러 가지 컴퓨터 프로그램들을 사용하다가 파일을 불러오거나 저장할 때 또는 글꼴 등을 바꾸려 할 때 새로운 창이 뜨고 그 창에서 데이터를 입력하면 입력한 내용대로 입력이 변하는 것을 보실 수 있습니다. 이때 새로 뜨는 창의 모양을 필요에 따라 다르지만 입력한 내용을 그대로 적용하는 목적은 동일합니다. 이러한 창의 디자인은 이미 정해져 있기 때문에 어디서나 같은 프로그램을 사용하면 동일한 디자인의 창이 뜨고 동일하게 작동합니다.

 

VBA 사용자 정의 폼은 자신이 원하는 모양으로 창의 디자인을 꾸밀 수 있습니다. 또한, 컨트롤 도구를 이용하여 원하는 방향으로 실행되도록 꾸밀 수도 있습니다. 이제 나의 폼을 만들기 위해 사용자 정의 폼에 대해 알아보겠습니다.

 

 

 

 

1. 사용자 정의 폼 디자인

 엑셀 메뉴에서 개발 도구-코드-Visual Basic을 클릭하여 VBE 창을 엽니다. VBE 창 메뉴에서 삽입-사용자 정의 폼을 클릭하여 사용자 정의 폼을 만듭니다.

 

[그림 1] 사용자 정의 폼과 도구 상자

[그림 1]과 같이 우측에 보이는 도구 상자가 함께 사용자 정의 폼이 생성됩니다.  도구 상자를 보시면 앞에서 알아봤던 양식 컨트롤이나 ActiveX 컨트롤 아이콘과 동일하다는 것을 알 수 있습니다. 도구 상자의 아이콘 위에 마우스 커서를 이동하면 아이콘의 이름이 뜹니다. 

 

도구 상자에서 레이블 컨트롤('A'라고 표시된 아이콘)을 마우스 클릭하여 선택하고 좌측에 보이는 사용자 정의 폼 위로 마우스 커서를 이동하여 드래그로 원하는 크기로 만든 후 마우스 버튼에서 손을 뗍니다.

만든 레이블에 '업체명'이라고 입력합니다. 그리고 만든 레이블을 마우스로 클릭하여 선택한 상태에서 Ctrl 키를 누른 상태로 만든 레이블을 마우스로 클릭하고 아래로 드래그하면 레이블이 복사가 됩니다. 이런 방식으로 레이블을 두 개 더 만들어 [그림 2]와 같이 사용자 정의 폼을 꾸밉니다.

 

[그림 2] 사용자 정의 폼에 레이블 작성

 

이제 도구 상자에서 레이블 컨트롤 바로 우측에 있는 텍스트 상자를 마우스로 클릭하고 동일한 방법으로 텍스트 상자를 사용자 정의 폼에 만들고 Ctrl 키를 이용하여 텍스트 상자 3개를 만듭니다. ([그림 3] 참조)

 

[그림 3] 텍스트 상자 만들기

이제 도구 상자에서 레이블 컨트롤 바로 아래에 있는 명령 단추를 클릭하여 사용자 정의 폼에 만들고 Ctrl 키를 이용하여 복사를 합니다.

 

[그림 4] 명령 단추

만든 명령 단추를 마우스로 클릭하면 VBE 창 좌측 아래에 있는 속성 창에 명령 단추 속성을 설정할 수 있습니다. 속성 창이 보이지 않으면 단축키 F4를 누르거나 보기 메뉴에서 속성 창을 클릭합니다. 속성 창에서 Caption 항목을 찾아 '확인'이라고 입력하면 명령 단추에 쓰인 글자가 '확인'으로 변경됩니다. 같은 방법으로 '취소' 버튼도 만듭니다.

 

이제 사용자 정의 폼을 마우스로 선택하고 속성 창에서 Caption 항목에 '거래처 관리'라고 입력하면 사용자 정의 폼 좌측 상단 이름이 'UserForm1'에서 '거래처 관리'로 바뀝니다.

 

간단하게 사용자 정의 폼 디자인이 어느 정도 마무리되었습니다. 이제 코딩을 통해 시트에 입력한 데이터가 저장되도록 하는 방법에 대해 알아보겠습니다.

 

 

 

2. 엑셀 시트에 데이터 입력하는 사용자 정의 폼 코딩

 코딩하기 전에 먼저 엑셀 시트에 입력될 데이터 구분을 위해 제목을 설정하겠습니다.

 

[그림 5] 엑셀 시트 제목 입력

[그림 5]와 같이 데이터 제목을 엑셀 시트에 작성합니다. 앞으로 사용자 정의 폼에서 입력된 데이터가 4행부터 해당 제목 밑에 입력되게 할 것입니다. 엑셀 시트에서 '거래처 관리'를 셀 병합 없이 가운데에 표시되도록 하는 방법은 아래 링크의 글을 확인하시기 바랍니다.

 

셀 병합없이 입력한 데이터를 가운데 배치하기

셀 병합없이 입력한 데이터를 가운데 배치하기

 

그리고, 개발 도구-컨트롤-삽입-ActiveX 컨트롤에서 명령 단추를 마우스 클릭하고 엑셀 시트 원하는 위치에 마우스를 클릭하고 드래그하여 버튼을 하나 만듭니다. 개발 도구-컨트롤-속성을 클릭하고 버튼의 이름을 '거래처 입력'으로 바꿉니다. (Caption 항목에서 변경)

이제 만든 버튼을 더블클릭하면 VBE 창이 열리면서 명령 단추에 대한 코딩 창이 뜹니다. 

 

[그림 6] 거래처 입력 버튼 VBA 코딩

[그림 6]과 같이 코딩을 합니다. 그리고 엑셀 시트의 거래처 입력 버튼을 클릭하면 우리가 만든 사용자 정의 폼이 출력되는 것을 보실 수 있습니다. 만약 버튼 클릭이 되지 않는다면 엑셀 메뉴 개발 도구-컨트롤-디자인 모드가 선택되어 있기 때문이므로 마우스로 한 번 클릭하여 해제해 줍니다.

 

사용자 정의 폼에 만든 '확인' 버튼을 더블 클릭하고 코딩창이 뜨면 아래와 같이 코딩합니다.

 

   Option Explicit
   Dim 입력줄수 As Integer

   Private Sub CommandButton1_Click()
      입력줄수 = WorksheetFunction.CountA(Range("B:B")) + 2
    
      Cells(입력줄수, 2) = TextBox1.Value
      Cells(입력줄수, 3) = TextBox2.Value
      Cells(입력줄수, 4) = TextBox3.Value
   End Sub

 

WorksheetFunction.CountA(Range("B:B")) : 시트 B열에 데이터가 입력되어 있는 셀의 행 갯수를 파악하여 그 값을 반환합니다. 

 

TextBox1 : 사용자 정의 폼에서 업체명 레이블 옆에 만든 텍스트 상자 이름

TextBox2 : 사용자 정의 폼에서 매입/매출 선택 레이블 옆에 만든 텍스트 상자 이름

TextBox3 : 사용자 정의 폼에서 담당자 이름 레이블 옆에 만든 텍스트 상자 이름

 

다음에는 '취소' 버튼을 더블 클릭하고 코딩창에 아래와 같이 코딩합니다.

 

   Private Sub CommandButton2_Click()
       Unload UserForm1
   End Sub

 

이제 코딩은 완성되었습니다. 엑셀 시트에 만든 '거래처 입력' 버튼을 누르고 텍스트 상자에 데이터를 입력하고 확인 버튼을 누르면 엑셀 시트에 데이터가 표시되는 것을 보실 수 있습니다. 또한, 취소 버튼을 클릭하면 거래처 관리 창은 닫힙니다.