MS Office - Excel 2010 - VBA 초보 사용자 정의 폼 사용하기

사용할 문서를 제작하면서 신기한 기능에 놀라고, 구현이 어려움에 놀라서 잊기 전에 정리해두고자 한다. ㅋ_ㅋ

유용한 기능들로 여러 방면으로 응용이 가능하리라 판단되기에 ~_~;

주요 사용 기능

- TextBox 에 데이터 입력 받고 입력받은 데이터 저장/불러오기

- ComboBox에 목록을 보여주고, 선택한 항목 데이터 저장/불러오기

- Radio(Option) Button 선택 후 선택한 항목 데이터 저장/불러오기

- MsgBox 출력 및 선택 항목에 따른 추가 액션

- Excel 통합문서 첨부하여 메일 발송

- SaveAs 기능을 통해 통합문서 다른 이름으로 저장

엑셀을 설치 후 Visual Basic for Application 기능을 사용하지 않았다면 관련 기능이 Disable 상태로 있는 것 같다.

"파일" > "옵션" 항목으로 엑셀 옵션 창을 열고 "리본 사용자 지정" > "기본 탭" > "개발도구"를 "추가" 하자

추가되면 탭 형태로 관련 메뉴를 확인 할 수 있다.

엑셀 문서에서 개발도구의 Visual Basic을 실행하면, VB Editor가 열린다.

"MS Excel 개체"에서 마우스 우클릭을 하면 "삽입" > 사용자 정의 폼을 확인할 수 있다.

좌측과 같이 도구상자의 컨트롤들을 이용하여 여러 UI를 꾸밀 수 있다.

VB 프로젝트의 "현재_통합_문서"에서 우클릭하면 코드를 볼 수 있다.

현재_통합_문서의 코드에서 위에서 생성한 사용자 정의 폼(난 frmMain이라 정의하였다)을 열어 주면

사용자가 엑셀문서를 실행하면 바로 사용자 정의 폼을 출력할 수 있다.

- 현재_통합_문서의 예시 코드

Private Sub Workbook_Open()

    frmMain.frmMain_Initialize '폼 초기화 함수
    frmMain.Show '폼 출력 함수

End Sub

구현 기능의 기능과 목적

엑셀 문서를 실행하면 아래처럼 문서가 열림과 동시에 사용자 폼이 바로 열리고 데이터를 폼을 통해서만 수정하도록 하게 하려고 한다.

폼 초기화 함수를 통해 문서가 실행될 때 특정 값(예로 기존에 저장된 값)들을 폼에서 보이게 할 수 있다.

폼 출력 함수를 통해 문서가 실행 될 때, 폼을 바로 보여줄 수 있다.

폼의 사용자 정의 폼에서 코드를 편집하자. (<F7>)

- 현재_통합_문서에서 실행한 초기화 함수를 정의한 예시 코드

Public Sub frmMain_Initialize()

    'Variable 선언 for OptionBox(Radio Button)
    Dim iVendor As Integer '제조사를 구분할 변수를 정수형으로 선언

    'Case 정보

    '엑셀 통합 문서의 시트 2의 D2 셀 값을 번호(텍스트박스) 항목에 보여준다.
    tb_CaseNum.Text = Sheet2.Range("D2").Value '번호
    cbb_PL_Name.Text = Sheet2.Range("D3").Value '제품명
    tb_Date.Text = Sheet2.Range("D6").Value '날짜

    'Option 버튼 선택에 따라 값을 기억하기 위한 변수 선언

iVendor = Sheet2.Range("D8").Value

    'Option Button 값의 True는 선택된 상태
    If iVendor = "1" Then frmMain.ob_HP.Value = True
    If iVendor = "2" Then frmMain.ob_Dell.Value = True
    If iVendor = "3" Then frmMain.ob_IBM.Value = True

End Sub

- 저장 버튼

Private Sub cb_Save_Click()

On Error Resume Next

'Variable 선언 for OptionBox(Radio Button)
Dim iVendor As Integer

    'Case 정보
    Sheet2.Range("D2") = tb_CaseNum.Text
    Sheet2.Range("D3") = cbb_PL_Name.Text
    Sheet2.Range("D6") = tb_Date.Text

    If frmMain.ob_HP.Value = True Then iVendor = "1"
    If frmMain.ob_Dell.Value = True Then iVendor = "2"
    If frmMain.ob_IBM.Value = True Then iVendor = "3"

    Sheet2.Range("D8") = iVendor

    '저장부분
    Dim strNewFile As Variant

    '폼 상의 데이터들을 엑셀 워크 시트에 저장

ActiveWorkbook.Saved = True

'파일 명 "GCE Elevation v2"를 기본으로 매크로를 포함하는 엑셀 문서 형식으로 저장하기 위해 파일명 지정
'Excel 2007 - 2010

strNewFile = Application.GetSaveAsFilename("GCE Elevation v2", filefilter:="Excel File include Macro(*.xlsm), *.xlsm")

'Excel 97 - 2003

'strNewFile = Application.GetSaveAsFilename("GCE Elevation v2", filefilter:="Excel File include Macro(*.xls), *.xls")

If strNewFile = False Then Exit Sub 'SaveAs 취소 시 에러 처리

    ActiveWorkbook.SaveAs strNewFile, 52    'Excel 2007 - 2010
    'ActiveWorkbook.SaveAs strNewFile, 56    'Excel 97-2003

End Sub

- 버튼 클릭 시 화면에 출력되는 내용

- 초기화 버튼

Public Sub funcReset()

'실제 폼을 초기화 하는 함수 - 그냥 노가다로 다 초기화 시켰다 ㅡ_ㅡ;
'Case 정보
frmMain.tb_CaseNum.Value = ""
frmMain.cbb_PL_Name.Value = ""
frmMain.tb_Date.Value = ""

If frmMain.ob_HP.Value = True Then frmMain.ob_HP.Value = False
If frmMain.ob_Dell.Value = True Then frmMain.ob_Dell.Value = False
If frmMain.ob_IBM.Value = True Then frmMain.ob_IBM.Value = False

End Sub

Private Sub cb_Reset_Click()

Dim iRtnMB As Integer

iRtnMB = MsgBox("데이터를 모두 지울까요?" & Chr(13) & "데이터의 필요 여부를 꼭 확인하세요", vbYesNo + vbDefaultButton2, "초기화")

If iRtnMB = vbYes Then funcReset '초기화 함수 실행

End Sub

- 버튼 클릭 시 화면에 출력되는 내용

- 종료 버튼

Private Sub cb_Close_Click()

    Dim iRtnMB As Integer

    iRtnMB = MsgBox("엑셀을 종료 할까요?" & Chr(13) & "저장 여부를 꼭 확인하세요", vbYesNo + vbDefaultButton2, "종료")

    If iRtnMB = vbYes Then Application.Quit '종료를 선택하면 엑셀을 종료한다

End Sub

- 버튼 클릭 시 화면에 출력되는 내용

- 종료 후 메일 발송 버튼

Private Sub cb_SaveAndSendEmail_Click()

On Error Resume Next

'Variable 선언 for OptionBox(Radio Button)
Dim iVendor As Integer

    '저장
    ActiveWorkbook.Saved = True

    '메일발송
    ActiveWorkbook.SendMail ""

End Sub

- 버튼 클릭 시 화면에 출력되는 내용

- 메인 윈도우 Close 버튼 제어
'Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'    Dim iRtnMB As Integer

'    If CloseMode = vbFormControlMenu Then

'        iRtnMB = MsgBox("엑셀을 종료 할까요?" & Chr(13) & "저장 여부를 꼭 확인하세요", vbYesNo + vbDefaultButton2, "종료")

'        If iRtnMB = vbYes Then ActiveWorkbook.Close
'        Cancel = True
'   End If
'End Sub

이 기능은 제작자 본인도 VBA 수정을 하기에 번거로움이 생기는 기능인 것 같다.

활용하기 나름이겠지만 .. ㅎ_ㅎ

폼 외의 엑셀 시트의 접근을 막기 위해 활용가능한 기능 같다.