ÇØ´ç ¿ä±¸»çÇ×À» ¹Ý¿µÇÏ¿© Äڵ带 ¼öÁ¤ÇØ µå¸± ÀÖ½À´Ï´Ù. ¾Æ·¡¿Í °°ÀÌ °¢ ½ºÅ©¸³Æ®¸¦ ¾÷µ¥ÀÌÆ® ÇØÁÖ¼¼¿ä.
ù ¹ø° À¯ÀúÆû ½ºÅ©¸³Æ® (³¯Â¥ Ãß°¡ ¹× Á¾·ù-Ç°¸í ÀÇÁ¸¼º Ãß°¡):
Private Sub UserForm_Initialize()
' µ¥ÀÌÅÍ ½ÃÆ®¿¡¼ ÄÞº¸¹Ú½º µ¥ÀÌÅ͸¦ ·Îµå
Dim wsData As Worksheet
Set wsData = ThisWorkbook.Sheets("µ¥ÀÌÅÍ ½ÃÆ®")
' Á¾·ù ·Îµå
cboType.Clear
Dim rngType As Range
Set rngType = wsData.Range("A2:A" & wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row)
Dim cell As Range
Dim typeDict As Object
Set typeDict = CreateObject("scripting.Dictionary")
For Each cell In rngType
If cell.Value <> "" And Not typeDict.exists(cell.Value) Then
cboType.AddItem cell.Value
typeDict.Add cell.Value, Nothing
End If
Next cell
' ´ã´çÀÚ ·Îµå
cboPerson.Clear
Dim rngPerson As Range
Set rngPerson = wsData.Range("C2:C" & wsData.Cells(wsData.Rows.Count, 3).End(xlUp).Row)
For Each cell In rngPerson
If cell.Value <> "" Then
cboPerson.AddItem cell.Value
End If
Next cell
' ¼ö·® ·Îµå
cboQuantity.Clear
Dim i As Integer
For i = 1 To 100 ' ¿øÇÏ´Â ¼ö·®±îÁö Ãß°¡
cboQuantity.AddItem i
Next i
' ÆûÀÇ ¸ðµç ÀÔ·Â Çʵ带 ÃʱâÈ
cboType.Value = ""
cboName.Clear ' ÃʱâÈÇÒ ¶§´Â Ç°¸í Çʵ带 ºñ¿öµÓ´Ï´Ù.
cboQuantity.Value = ""
cboPerson.Value = ""
txtNote.Value = ""
txtDate.Value = Date ' ¿À´Ã ³¯Â¥·Î ÃʱâÈÇÕ´Ï´Ù. ¶Ç´Â ÇÊ¿äÇÑ °æ¿ì "³¯Â¥ ¼±ÅÃ" ¶óº§À» »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.
End Sub
Private Sub cboType_Change()
' Á¾·ù ¼±Åà ½Ã Ç°¸í ¾÷µ¥ÀÌÆ®
Dim wsData As Worksheet
Set wsData = ThisWorkbook.Sheets("µ¥ÀÌÅÍ ½ÃÆ®")
Dim selectedType As String
selectedType = cboType.Value
cboName.Clear
If selectedType <> "" Then
Dim rngName As Range
Set rngName = wsData.Range("A2:B" & wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row)
Dim cell As Range
For Each cell In rngName
If cell.Value = selectedType And cell.Offset(, 1).Value <> "" Then
cboName.AddItem cell.Offset(, 1).Value
End If
Next cell
End If
End Sub
Private Sub btnEnter_Click()
Call ÀÔ°í
End Sub
Private Sub btnExit_Click()
Call Ãâ°í
End Sub
Private Sub txtDate_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
' ³¯Â¥ ¼±ÅÃÀ» À§ÇÑ Ä¶¸°´õ È£Ãâ
Dim calendarForm As Object
Set calendarForm = CreateObject("MSComCtl2.MonthView.2") ' MSCOMCT2.OCX ÄÄÆ÷³ÍÆ® ÇÊ¿ä (µî·Ï Çʼö)
With calendarForm
.MaxDate = Date
.Show
txtDate.Value = calendarForm.Value
End With
End Sub
µÎ ¹ø°´Â 1¹ø ¸ðµâ (³¯Â¥ Çʵå Ãß°¡):
Sub ÀÔ°í()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim ³¯Â¥ As Date, Á¾·ù As String, Ç°¸í As String, ¼ö·® As Integer, ´ã´çÀÚ As String, ºñ°í As String
' UserForm¿¡¼ µ¥ÀÌÅÍ °¡Á®¿À±â
³¯Â¥ = UserForm1.txtDate.Value
Á¾·ù = UserForm1.cboType.Value
Ç°¸í = UserForm1.cboName.Value
¼ö·® = UserForm1.cboQuantity.Value
´ã´çÀÚ = UserForm1.cboPerson.Value
ºñ°í = UserForm1.txtNote.Value
' ½ÃÆ® ÇÒ´ç
Set ws1 = ThisWorkbook.Sheets("Àç°í ¸®½ºÆ®")
Set ws2 = ThisWorkbook.Sheets("ÀÔÃâ°í ±â·Ï ¸®½ºÆ®")
' ÀÔÃâ°í ±â·Ï ¸®½ºÆ®¿¡ Ãß°¡
With ws2
Dim LastRow As Long
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(LastRow, 1).Value = ³¯Â¥ ' ³¯Â¥
.Cells(LastRow, 2).Value = Á¾·ù
.Cells(LastRow, 3).Value = Ç°¸í
.Cells(LastRow, 4).Value = ¼ö·®
.Cells(LastRow, 5).Value = ´ã´çÀÚ
.Cells(LastRow, 6).Value = ºñ°í
.Cells(LastRow, 7).Value = "ÀÔ°í"
End With
' Àç°í ¸®½ºÆ®¿¡ ±âÁ¸ ¼ö·® ¾÷µ¥ÀÌÆ® ¶Ç´Â Ãß°¡
Dim rg As Range
Set rg = ws1.Range("A2:B" & ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row)
Dim found As Range
Set found = rg.Find(Ç°¸í)
If Not found Is Nothing Then
found.Offset(, 2).Value = found.Offset(, 2).Value + ¼ö·®
Else
Dim NewRow As Long
NewRow = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row + 1
ws1.Cells(NewRow, 1).Value = Á¾·ù
ws1.Cells(NewRow, 2).Value = Ç°¸í
ws1.Cells(NewRow, 3).Value = ¼ö·®
End If
UserForm1.Hide
End Sub
Sub Ãâ°í()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim ³¯Â¥ As Date, Á¾·ù As String, Ç°¸í As String, ¼ö·® As Integer, ´ã´çÀÚ As String, ºñ°í As String
' UserForm¿¡¼ µ¥ÀÌÅÍ °¡Á®¿À±â
³¯Â¥ = UserForm1.txtDate.Value
Á¾·ù = UserForm1.cboType.Value
Ç°¸í = UserForm1.cboName.Value
¼ö·® = UserForm1.cboQuantity.Value
´ã´çÀÚ = UserForm1.cboPerson.Value
ºñ°í = UserForm1.txtNote.Value
' ½ÃÆ® ÇÒ´ç
Set ws1 = ThisWorkbook.Sheets("Àç°í ¸®½ºÆ®")
Set ws2 = ThisWorkbook.Sheets("ÀÔÃâ°í ±â·Ï ¸®½ºÆ®")
' ÀÔÃâ°í ±â·Ï ¸®½ºÆ®¿¡ Ãß°¡
With ws2
Dim LastRow As Long
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(LastRow, 1).Value = ³¯Â¥ ' ³¯Â¥
.Cells(LastRow, 2).Value = Á¾·ù
.Cells(LastRow, 3).Value = Ç°¸í
.Cells(LastRow, 4).Value = ¼ö·®
.Cells(LastRow, 5).Value = ´ã´çÀÚ
.Cells(LastRow, 6).Value = ºñ°í
.Cells(LastRow, 7).Value = "Ãâ°í"
End With
' Àç°í ¸®½ºÆ®¿¡¼ ±âÁ¸ ¼ö·® ¾÷µ¥ÀÌÆ®
Dim rg As Range
Set rg = ws1.Range("A2:B" & ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row)
Dim found As Range
Set found = rg.Find(Ç°¸í)
If Not found Is Nothing Then
found.Offset(, 2).Value = found.Offset(, 2).Value - ¼ö·®
Else
MsgBox "ÇØ´ç Ç°¸íÀÌ Àç°í ¸®½ºÆ®¿¡ ¾ø½À´Ï´Ù."
End If
UserForm1.Hide
End Sub
¼¼ ¹ø°´Â 2¹ø ¸ðµâ (»ç¿ëÀÚ Æû ÃʱâÈ ¹× ³¯Â¥ Çʵå Ãß°¡):
Sub ShowEnterForm()
' À¯ÀúÆû ÃʱâÈ ¹× ¼³Á¤
With UserForm1
.Caption = "ÀÔ°í Á¤º¸ ÀÔ·Â"
.btnEnter.Visible = True
.btnExit.Visible = False
.txtDate.Visible = True
.lblDate.Visible = True
.Show
End With
End Sub
Sub ShowExitForm()
' À¯ÀúÆû ÃʱâÈ ¹× ¼³Á¤
With UserForm1
.Caption = "Ãâ°í Á¤º¸ ÀÔ·Â"
.btnEnter.Visible = False
.btnExit.Visible = True
.txtDate.Visible = True
.lblDate.Visible = True
.Show
End With
End Sub
À§ÀÇ Äڵ忡¼´Â À¯ÀúÆû¿¡ ³¯Â¥ Çʵå(txtDate
)¸¦ Ãß°¡ÇÏ°í, Á¾·ù¿Í Ç°¸í °£ÀÇ ÀÇÁ¸¼ºÀ» ¼³Á¤ÇÏ¿´½À´Ï´Ù. ¶ÇÇÑ ³¯Â¥ ¼±ÅÃÀ» À§ÇÑ Ä¶¸°´õ ÆûÀ» Ãß°¡ÇÏ¿´À¸´Ï ÇÊ¿ä¿¡ µû¶ó MSCOMCTL.OCX ÆÄÀÏÀ» ÀûÀýÈ÷ µî·ÏÇØ¾ß ÇÕ´Ï´Ù.