CODING FORM INVOICE | EXCEL & VBA TUTORIAL
================================================
Option Explicit
Private Sub CMDADD_Click()
Dim DBINVOICE As Object
Set DBINVOICE = [Link]("B23").End(xlUp)
If [Link] = "" _
Or [Link] = "" _
Or [Link] = "" _
Or [Link] = "" _
Or [Link] = "" _
Or [Link] = "" _
Or [Link] = "" _
Or [Link] = "" Then
Call MsgBox("Harap isi data barang dengan lengkap", vbInformation, "Data Barang")
Else
[Link](1, 0).value = [Link]
[Link](1, 1).value = [Link]
On Error Resume Next
[Link](1, 2).value = CDec([Link])
[Link](1, 3).value = [Link]
[Link](1, 4).value = CDec([Link])
Call MsgBox("Data barang telah disimpan", vbInformation, "Data Barang")
Call AmbilData
[Link] = [Link]("F26").value
[Link] = [Link]("F27").value
[Link] = [Link]("F28").value
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
End If
End Sub
Private Sub CMDBARU_Click()
Dim X As Long
X = [Link]("H10").value + 1
[Link]("H10").value = X
Select Case MsgBox("Anda akan membuat Invoice baru" _
& vbCrLf & "Apakah anda yakin?" _
, vbYesNo Or vbQuestion Or vbDefaultButton1, "Invoice Baru")
Case vbNo
Exit Sub
Case vbYes
End Select
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = True
[Link] = False
[Link]("B13:F23").value = ""
If [Link]("I10").value = 1 Then
[Link] = "INV-100000" & X
End If
If [Link]("I10").value = 2 Then
[Link] = "INV-10000" & X
End If
If [Link]("I10").value = 3 Then
[Link] = "INV-1000" & X
End If
If [Link]("I10").value = 4 Then
[Link] = "INV-100" & X
End If
If [Link]("I10").value = 5 Then
[Link] = "INV-10" & X
End If
[Link] = False
End Sub
Private Sub CMDCARI_Click()
[Link]
End Sub
Private Sub CMDCLEAR_Click()
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = True
[Link] = True
End Sub
Private Sub CMDCLEARALL_Click()
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = False
[Link] = True
[Link]("B13:F23").value = ""
End Sub
Private Sub CMDEXPORT_Click()
[Link] = False
Dim InvoiceRng As Range
Dim NamaFile As String
Dim DBINVOICE As Object
Set DBINVOICE = [Link]("A1000000").End(xlUp)
If [Link] = "" _
Or [Link] = "" _
Or [Link] = "" _
Or [Link] = "" Then
Call MsgBox("Harap isi terlebih dahulu data invoice", vbInformation, "Data
Invoice")
Else
Set InvoiceRng = [Link]("B1:F42")
NamaFile = "Invoice_" & [Link] & "_" & Format(Now(),
"DDMMYYYY_HHMMSS") & ".Pdf"
[Link](1, 0).value = "=ROW()-ROW($A$5)"
[Link](1, 1).value = [Link]
[Link](1, 2).value = [Link]
[Link](1, 3).value = [Link]
[Link](1, 4).value = Format(CDate([Link]), "MM/DD/YYYY")
[Link](1, 5).value = [Link] & NamaFile
NamaFile = [Link] & NamaFile
[Link] _
Type:=xlTypePDF, _
Filename:=NamaFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=True, _
OpenafterPublish:=True
Call AmbilInvoice
Call MsgBox("Invoice telah di export ke Pdf", vbInformation, "Export Invoice")
[Link] = True
[Link] = True
End If
End Sub
Private Sub CMDHAPUS_Click()
Dim DBINVOICE As Object
Set DBINVOICE = [Link]("B13:B23").Find(What:=[Link],
LookIn:=xlValues)
If [Link] = "" Then
Call MsgBox("Harap pilih nama barang terlebih dahulu", vbInformation, "Nama
Barang")
Else
Select Case MsgBox("Anda akan menghapus barang" _
& vbCrLf & "Apakah anda yakin?" _
, vbYesNo Or vbQuestion Or vbDefaultButton1, "Hapus Barang")
Case vbNo
Exit Sub
Case vbYes
End Select
[Link](0, 0).value = ""
[Link](0, 1).value = ""
[Link](0, 2).value = ""
[Link](0, 3).value = ""
[Link](0, 4).value = ""
Call MsgBox("Data barang telah dihapus", vbInformation, "Data Barang")
Call UrutData
Call AmbilData
[Link] = [Link]("F26").value
[Link] = [Link]("F27").value
[Link] = [Link]("F28").value
[Link] = True
[Link] = True
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
End If
End Sub
Private Sub CMDTGL_Click()
[Link] = 1
Call AdvancedCalendar
End Sub
Private Sub CMDUPDATE_Click()
Dim DBINVOICE As Object
Set DBINVOICE = [Link]("B13:B23").Find(What:=[Link],
LookIn:=xlValues)
If [Link] = "" Then
Call MsgBox("Harap pilih nama barang terlebih dahulu", vbInformation, "Nama
Barang")
Else
On Error Resume Next
[Link](0, 2).value = CDec([Link])
[Link](0, 3).value = [Link]
[Link](0, 4).value = CDec([Link])
Call MsgBox("Data barang telah diubah", vbInformation, "Data Barang")
Call AmbilData
[Link] = [Link]("F26").value
[Link] = [Link]("F27").value
[Link] = [Link]("F28").value
[Link] = True
[Link] = True
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
End If
End Sub
Private Sub TABELINVOICE_DblClick(ByVal Cancel As [Link])
On Error GoTo EXCELVBA
Dim SumberData, CellAktif As Long
[Link] = [Link]
[Link] = [Link](1)
[Link] = [Link](3)
[Link] = [Link](2)
[Link] = [Link](4)
[Link] = False
[Link] = False
[Link]
SumberData = Sheets("INVOICE").Cells([Link], "B").End(xlUp).Row
Sheets("INVOICE").Range("B13:B" & SumberData).Find(What:=[Link],
LookIn:=xlValues, LookAt:=xlWhole).Activate
CellAktif = [Link]
[Link]
Exit Sub
EXCELVBA:
Call MsgBox("Harap klik 2x pada data yang tersedia", vbInformation, "Pilih Data")
End Sub
Private Sub TXTALAMAT_Change()
[Link]("B9").value = [Link]
End Sub
Private Sub TXTDIKIRIM_Change()
[Link]("B8").value = [Link]
End Sub
Private Sub TXTHARGA_Change()
On Error Resume Next
[Link] = Format([Link], "#,###")
End Sub
Private Sub TXTINVOICE_Change()
[Link]("E10").value = [Link]
End Sub
Private Sub TXTPAJAK_Change()
On Error Resume Next
[Link] = Format([Link], "#,###")
End Sub
Private Sub TXTQTY_Change()
Dim A As Currency
Dim B As Integer
On Error Resume Next
A = [Link]
B = [Link]
[Link] = A * B
End Sub
Private Sub TXTSUBTOTAL_Change()
On Error Resume Next
[Link] = Format([Link], "#,###")
End Sub
Private Sub TXTTANGGAL_Change()
[Link]("E8").value = [Link]
End Sub
Private Sub AmbilData()
Dim DBBARANG As Long
Dim iRow As Long
iRow = [Link]("B" & [Link]).End(xlUp).Row
DBBARANG = [Link]([Link]("B13:B23"))
If DBBARANG = 0 Then
[Link] = ""
Else
[Link] = "INVOICE!B13:F" & iRow
End If
End Sub
Private Sub AmbilInvoice()
Dim DBINVOICE As Long
Dim iRow As Long
iRow = [Link]("A" & [Link]).End(xlUp).Row
DBINVOICE = [Link]([Link]("A6:A1000000"))
If DBINVOICE = 0 Then
[Link] = ""
Else
[Link] = "DATAINVOICE!A6:F" & iRow
End If
End Sub
Private Sub TXTTOTAL_Change()
On Error Resume Next
[Link] = Format([Link], "#,###")
End Sub
Private Sub TXTTOTALHARGA_Change()
On Error Resume Next
[Link] = Format([Link], "#,###")
End Sub
Private Sub UserForm_Initialize()
Call AmbilData
[Link] = False
[Link] = [Link]("F26").value
[Link] = [Link]("F27").value
[Link] = [Link]("F28").value
[Link] = [Link]("E10").value
[Link] = [Link]("E8").value
[Link] = [Link]("B8").value
[Link] = [Link]("B9").value
End Sub