VBA로 업체별 납품 품목 자동으로 작성되어 정리되도록 하기 I

업무에서 거래처별 납품 목록을 엑셀을 사용하여 관리하는 경우가 많습니다. 이때 일일이 기록하면서 좀 더 빠르고 편리하게 하는 방법은 없을까? 하는 생각을 누구나 한 번쯤 하게 됩니다. 이번에는 VBA를 이용하여  이런 생각을 실제 실현해보고자 합니다.

 

 

 

 

1. 엑셀에 기본 시트 만들기

 우선 거래업체 및 품목 관련 데이터를 정리한 시트를 만들어 엑셀 파일로 저장하겠습니다.

엑셀 파일 첫 번째 시트에는 거래업체, 품목 및 가격을 정리해 놓습니다.

[그림 1] 엑셀 파일 모습

[그림 1]과 같이 sheet1에는 거래 업체명, 물품 및 가격을 입력해 놓습니다. 그리고 업체명으로 이름 지은 시트를 추가합니다. 업체명으로 이름 지은 시트에도 '일자', '물품', '가격'이라는 제목을 작성해 놓습니다.

이제 sheet1에 개발도구-삽입-명령 단추(ActiveX 컨트롤)을 클릭하여 버튼 하나를 원하는 위치에 만들어 놓습니다.

 

만든 버튼을 선택한 상태에서 개발도구-속성을 클릭하여 속성 창을 엽니다.

속성 창에서 버튼 배경색(BackColor), 글자 모양 및 크기(Font), 글자색(ForeColor) 등을 변경하여 원하는 디자인을 합니다.

 

[그림 2] 버튼 만들기 및 속성창

저는 [그림 2]와 같이 버튼을 만들었습니다. 속성 창을 보시면 작성한 버튼 속성을 확인하실 수 있습니다.

버튼 작성이 완료되었으니 개발도구-디자인모드를 클릭하여 선택 해제하여 버튼 디자인을 종료합니다.

 

 

 

2. 입력창 디자인 하기

 입력버튼을 누르면 입력창이 뜨고 입력된 내용을 해당 업체의 시트에 자동으로 입력되도록 하려면 먼저 버튼을 눌렀을 때 출력될 입력창을 디자인해야 합니다.

개발도구-Visual Basic을 클릭하여 Visual Basic Editor창을 엽니다.

그리고, 삽입-사용자 정의 폼을 클릭하여 사용자 정의 창을 띄웁니다.

[그림 3] 거래입력창 디자인

[그림 3]과 같이 거래 입력창을 디자인합니다.

 

UserForm1의 속성의 Caption에서 창 이름을 '거래 입력'이라고 입력합니다.

그리고, 도구상자에서도구 상자에서 레이블을 선택하여 원하는 위치에 드래그하여 만든 후 '거래처 입력'이라고 입력합니다. 그 바로 아래에는 도구 상자에서 콤보 상자를 선택하여 역시 드래그하여 생성합니다.

'물품 선택'도 같은 방법으로 레이블을 선택하여 만듭니다. 그 아래에는 도구상자에서 목록 상자를 선택하여 드래그하여 생성합니다.

도구 상자의 명령단추를 클릭하여 우측에 버튼 두 개를 생성하여 하나의 이름은 '확인', 나머지는 '종료'라 입력합니다. 버튼을 만들 때 먼저 버튼 한 개를 만들어 드래그로 알맞은 크기로 만든 후 만든 버튼이 선택된 상태에서 Ctrl 키를 누른 상태에서 마우스로 버튼을 드래그하면 복사가 됩니다. 그 후 각 버튼을 마우스로 선택한 상태에서 속성의 Caption에 '확인'과 '종료'를 입력하면 됩니다.

그리고 품목이 선택되었을 때 금액이 뜨도록 하기 위해 [그림 3]의 우측 아래와 같이 레이블을 두 개 만듭니다. (금액이라 입력된 레이블에 물품 가격이 뜨도록 할 것입니다.)

 

sheet1에 만들어 놓은 데이터를 이름 정의하여 사용이 편리하게 하겠습니다.

업체명으로 되어 있는 B열의 데이터를 전체 선택하여 이름을 '업체'로 합니다. 그리고 각각 '품목', '가격'도 동일하게 이름 정의합니다. 이때 아래에 빈 셀을 더 추가해서 선택하면 나중에 업체 등을 추가해도 거래 입력창에서 인식이 되므로 여유를 주어 이름 정의하시는 걸 추천드립니다.

 

그리고, 콤보상자의 속성에서 RowSource에 '업체'라고 입력합니다.

목록 상자의 속성에서 RowSource에 '품목'이라고 입력합니다.

이름 정의하는 방법은 엑셀 시작해보기 II를 참조하시기 바랍니다.

 

이제 거래처 관리를 위한 디자인은 모두 작성했습니다. 다음에는 코드를 작성하여 실제 실행해 보겠습니다.

 

VBA로 업체별 납품 품목 자동으로 작성되어 정리되도록 하기 II

VBA로 업체별 납품 품목 자동으로 작성되어 정리되도록 하기 II