■ 시트 구성
기본 구성 단계로 가계부 작성 시 필요한 정보인 자산, 수입 분류, 지출 분류, 자산 현황, 가계부 기록하는 시트로 구성합니다. 차후 통계, 월별 필터, 정렬 기능 등을 추가할 예정입니다.
■ 분류
분류는 총 3가지로 구분하여 구성합니다. 3개 시트는 공통으로 2행은 그룹명, 3행부터는 각 그룹의 세부 항목을 입력합니다. 자산분류 입력 예시로 2행에 "은행" 그룹명을 작성하고 그 아래부터 세부 항목인 "신한은행, 우리은행, 국민은행" 등으로 입력합니다.
수입 분류, 지출 분류도 동일한 방식으로 그룹명 지정 후 세무항목을 입력하는 방식으로 정리합니다.
■ 가계부 - 기본 설명
가계부에 기록할 수 있는 항목은 3가지입니다. 수입, 지출, 이체로 구성되어 있고 B열 구분으로 작성됩니다. 자동화, 통계자료 정리할 때 필수 요소이므로 구분해서 사용해야 합니다. 입력하면 구분별로 금액의 글씨 색이 변경됩니다. 수입은 파란색, 지출은 빨간색, 이체는 회색으로 자동으로 변경됩니다. 조건부 서식의 수식을 사용하여 서식 지정으로 아래와 같이 수식을 적용합니다.
두 번째 사진과 같이 수식을 입력하고 서식에서 원하는 서식으로 변경합니다. 변경 후 첫 번째 사진과 같이 금액이 들어있는 열 전체로 적용합니다.
기록방식은 셀에 직접 입력도 가능하며 우측상단에 "등록" 버튼을 누르면 실행되는 폼을 사용하여 등록합니다.
폼 상단에 수입, 지출, 이체 클릭 가능할 수 있는 버튼으로 구성되어 있습니다. 선택된 항목은 노란색으로 변경됩니다. 최초 실행 시에는 입력 빈도가 높은 지출이 선택된 상태로 실행됩니다. 이체를 선택할 경우에는 자산이 출금으로 변경되고, 분류는 입금으로 변경됩니다.
| Sub select_수입() '# &HC0FFFF = 노랑 / &HE0E0E0 = 회색 lb_back_1.BackColor = &HC0FFFF '# 수입 라벨 lb_back_2.BackColor = &HE0E0E0 '# 지출 라벨 lb_back_3.BackColor = &HE0E0E0 '# 이체 라벨 Label2 = "자 산" Label3 = "분 류" TextBox1.Text = "" TextBox2.Text = "" TextBox3.Text = "" TextBox4.Locked = False '# 이체를 선택할 경우 True / 내용 입력 불가 TextBox4.Text = "" End Sub Sub select_지출() lb_back_1.BackColor = &HE0E0E0 lb_back_2.BackColor = &HC0FFFF lb_back_3.BackColor = &HE0E0E0 Label2 = "자 산" Label3 = "분 류" TextBox1.Text = "" TextBox2.Text = "" TextBox3.Text = "" TextBox4.Locked = False TextBox4.Text = "" End Sub Sub select_이체() lb_back_1.BackColor = &HE0E0E0 lb_back_2.BackColor = &HE0E0E0 lb_back_3.BackColor = &HC0FFFF Label2 = "출 금" Label3 = "입 금" TextBox1.Text = "" TextBox2.Text = "" TextBox3.Text = "" TextBox4.Locked = True TextBox4.Text = "내용 입력불가" End Sub |
날짜는 등록 폼 실행 시 오늘 날짜로 입력됩니다. 날짜를 변경해야 될 경우 날짜 라벨을 클릭하면 날짜선택 폼이 열립니다. 날짜 선택 폼 실행 시 오늘 날짜가 기본값으로 입력됩니다. 다운 목록을 선택하여 원하는 날짜로 변경 한 후 입력 버튼을 클릭하면 변경됩니다.
| Private Sub UserForm_Activate() Dim date_Y, date_M, date_D, last_day Dim i date_Y = Year(Date) date_M = Month(Date) date_D = Day(Date) For i = -10 To 10 '# 당일 날짜를 기준으로 ±10년 // 기간을 더 길게 조정하고 싶을 경우 숫자 수정. cmb1.AddItem date_Y + i Next i For i = 1 To 12 cmb2.AddItem i Next i last_day = Day(WorksheetFunction.EoMonth(Date, 0)) For i = 1 To last_day cmb3.AddItem i Next i cmb1 = date_Y '# 년 콤보박스 cmb2 = date_M '# 월 콤보박스 cmb3 = date_D '# 일 콤보박스 Form_날짜선택.Caption = "날짜선택" End Sub Private Sub cmb2_Change() Dim x '# 월을 변경 할 경우 해당 년/월에 맞춰서 일을 변경합니다. If Form_날짜선택.Caption = "날짜선택" Then x = DateSerial(cmb1.Value, cmb2.Value, cmb3.Value) cmb3.Clear cmb3 = 1 last_day = Day(WorksheetFunction.EoMonth(x, 0)) For i = 1 To last_day cmb3.AddItem i Next i End If End Sub Private Sub CommandButton1_Click() '# 입력 버튼 클릭 시 실행 / 등록 폼 날짜에 선택한 날째 입력. change_date = DateSerial(cmb1.Value, cmb2.Value, cmb3.Value) Form_등록.tb_date = Format(change_date, "yyyy-mm-dd (aaa)") Unload Me End Sub Private Sub CommandButton2_Click() Unload Me End Sub |
등록 폼에서 자산 또는 분류, 이체인 경우에는 입금, 출금 글자 부분 클릭하면 가운데 폼이 실행됩니다. 자산과 분류 클릭 시 실행되는 폼의 양식은 동일합니다. 자산을 클릭할 경우 자산분류 시트에 입력한 내용을 가져오고 폼 상단의 제목(캡션)은 자산으로 표시됩니다. 분류를 클릭할 경우 수입 분류 또는 지출 분류 시트에서 내용을 가져오고 폼 상단의 제목(캡션)은 분류로 표시됩니다.
자산 또는 분류 입력 폼에 2개의 리스트가 있습니다. 왼쪽은 그룹이고 그룹명을 선택하면 해당하는 그룹의 세부 항목이 오른쪽 리스트에 나타납니다. 왼쪽에 그룹을 선택할 때마다 해당 그룹에 맞는 세부 항목으로 변경됩니다. 오른쪽에 나타난 세부 항목을 더블클릭하면 등록 폼에 자산 또는 분류의 텍스트 박스로 입력됩니다. 왼쪽이 Listbox1, 오른쪽이 Listbox2 입니다.
| Public Sheet_name As String Private Sub UserForm_Activate() Dim Wsf As WorksheetFunction Set Wsf = WorksheetFunction Dim Count_group As Long Call m_sheet_name Count_group = Sheets(Sheet_name).Range("B2", Sheets(Sheet_name).Range("B2").End(xlToRight)).Columns.Count ListBox1.ColumnCount = 1 For i = 1 To Count_group With ListBox1 .AddItem .List(i - 1, 0) = Sheets(Sheet_name).Range("A2").Offset(0, i) End With Next i End Sub Private Sub ListBox1_Click() Dim List1_index As Long Dim select_List As String Dim Wsf As WorksheetFunction Set Wsf = WorksheetFunction Dim c As Long ListBox2.Clear List1_index = ListBox1.ListIndex select_List = ListBox1.List(List1_index) Call m_sheet_name c = Wsf.Match(select_List, Sheets(Sheet_name).Rows("2:2"), 0) r = Sheets(Sheet_name).Range("A2").Offset(0, c - 1).End(xlDown).Row - 2 If Sheets(Sheet_name).Range("A2").Offset(1, c - 1) = "" Then Exit Sub '# 하위 목록이 없는 경우 종료 ListBox2.ColumnCount = 1 '# 왼쪽 리스트박스 선택 시 오른쪽 리스트 박스에 하위목록 리스트 생성 For i = 1 To r With ListBox2 .AddItem .List(i - 1, 0) = Sheets(Sheet_name).Range("A2").Offset(i, c - 1) End With Next i End Sub Private Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean) On Error GoTo exit_sub: str_list1 = ListBox1.List(ListBox1.ListIndex) str_list2 = ListBox2.List(ListBox2.ListIndex) '# 하위 목록 더블클릭 실행 시 등록 폼으로 선택 값 입력 If Sheet_name = "자산분류" Then If Form_팝업.Caption = "이체(입금)" Then Form_등록.TextBox2 = str_list1 & "≫" & str_list2 Else Form_등록.TextBox1 = str_list1 & "≫" & str_list2 End If ElseIf Sheet_name = "수입분류" Or Sheet_name = "지출분류" Then Form_등록.TextBox2 = str_list1 & "≫" & str_list2 End If Unload Me exit_sub: End Sub Sub m_sheet_name() If Form_팝업.Caption = "분류" Then If Form_등록.lb_back_1.BackColor = &HC0FFFF Then Sheet_name = "수입분류" If Form_등록.lb_back_2.BackColor = &HC0FFFF Then Sheet_name = "지출분류" ElseIf Form_등록.lb_back_3.BackColor = &HC0FFFF Then Sheet_name = "자산분류" ElseIf Form_팝업.Caption = "자산" Then Sheet_name = "자산분류" End If End Sub Private Sub CommandButton1_Click() Unload Me End Sub |
이제 금액을 입력해주면 되는데 텍스트 박스에 직접 입력할 수 있습니다. 텍스트만 입력가능하게 적용되어 있습니다. 금액 아래에 있는 -, 100만, 10만, 5만, 1만 버튼을 클릭하여 입력할 수 있습니다. 100만 부터 1만을 클릭하면 해당하는 단위만큼 추가됩니다. 10,000원 입력된 상태에서 5만 버튼을 클릭하면 60,000원으로 변경됩니다. 맨 왼쪽에 마이너스(-)는 맨 앞쪽으로 들어갑니다. 마이너스 값으로 입력해야 할 경우 사용합니다.
| Private Sub CommandButton8_Click() '# 금액에 마이너스(-) 추가/제거 If InStr(1, TextBox3.Text, "-") > 0 Then TextBox3.Text = Replace(TextBox3, "-", "") ElseIf TextBox3.Text <> "" Then '# 입력 값이 있는 경우에만 실행 TextBox3.Text = "-" & TextBox3 End If End Sub Private Sub CommandButton1_Click() '# 금액 추가 버튼 If TextBox3 = "" Then TextBox3.Value = 0 TextBox3 = TextBox3.Value + 1000000 End Sub Private Sub TextBox3_Change() TextBox3.Value = Format(TextBox3.Value, "#,##0") '# 3자리마다 콤마(,)표시 End Sub Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) '# 금액칸, 숫자만 입력가능하게 If (KeyCode >= 48 And KeyCode <= 57) Or _ (KeyCode >= 96 And KeyCode <= 105) Or _ KeyCode = 109 Or _ KeyCode = 189 Or _ KeyCode = vbKeyBack Or _ KeyCode = vbKeyDelete Or _ KeyCode = vbKeyLeft Or _ KeyCode = vbKeyRight Or _ KeyCode = vbKeyUp Or _ KeyCode = vbKeyDown Or _ KeyCode = vbKeyHome Or _ KeyCode = vbKeyEnd Or _ KeyCode = vbKeyShift Then Else KeyCode = 0 End If End Sub |
다음에는 통계 시트 추가 및 매크로 기능을 추가하겠습니다.