Scribd Logo
Upload

Welcome to Scribd!

  • 63 views

    Coding Form Barang

    Uploaded by

    Risky
    The document contains coding for a form used to manage product data in Excel. It includes code to: 1) Initialize form controls like dropdown menus and link the form to the product data table. 2) Add, update, delete and search product data records by copying values to and from the data table. 3) Manage product images by copying files to a folder when records are added or updated. 4) Reset and clear form controls for new data entry. The form allows the user to easily view, modify, add and remove product records from the linked data table.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Coding Form Barang

    Uploaded by

    Risky
    63 views8 pages
    The document contains coding for a form used to manage product data in Excel. It includes code to: 1) Initialize form controls like dropdown menus and link the form to the product data table. 2) Add, update, delete and search product data records by copying values to and from the data table. 3) Manage product images by copying files to a folder when records are added or updated. 4) Reset and clear form controls for new data entry. The form allows the user to easily view, modify, add and remove product records from the linked data table.

    Original Title

    CODING FORM BARANG

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document contains coding for a form used to manage product data in Excel. It includes code to: 1) Initialize form controls like dropdown menus and link the form to the product data table. 2) Add, update, delete and search product data records by copying values to and from the data table. 3) Manage product images by copying files to a folder when records are added or updated. 4) Reset and clear form controls for new data entry. The form allows the user to easily view, modify, add and remove product records from the linked data table.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    63 views8 pages

    Coding Form Barang

    Uploaded by

    Risky
    The document contains coding for a form used to manage product data in Excel. It includes code to: 1) Initialize form controls like dropdown menus and link the form to the product data table. 2) Add, update, delete and search product data records by copying values to and from the data table. 3) Manage product images by copying files to a folder when records are added or updated. 4) Reset and clear form controls for new data entry. The form allows the user to easily view, modify, add and remove product records from the linked data table.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 8

    CODING FORM BARANG

    1. CODING USERFORM INITIALIZE


    On Error Resume Next
    Me.TABELBARANG.RowSource = Sheet2.Range("TBBARANG").Address(External:=True)
    With CMBSATUAN
    .AddItem "Kg"
    .AddItem "Liter"
    .AddItem "PCS"
    .AddItem "Kotak"
    .AddItem "Kardus"
    .AddItem "Buah"
    .AddItem "Lusin"
    End With

    With CMBKATEGORI
    .AddItem "Makanan"
    .AddItem "Minuman"
    .AddItem "Snack"
    .AddItem "Mie Instan"
    End With

    With CMBSATUAN1
    .AddItem "Kg"
    .AddItem "Liter"
    .AddItem "PCS"
    .AddItem "Kotak"
    .AddItem "Kardus"
    .AddItem "Buah"
    .AddItem "Lusin"
    End With
    With CMBSATUAN2
    .AddItem "Kg"
    .AddItem "Liter"
    .AddItem "PCS"
    .AddItem "Kotak"
    .AddItem "Kardus"
    .AddItem "Buah"
    .AddItem "Lusin"
    End With
    With CMBSATUAN3
    .AddItem "Kg"
    .AddItem "Liter"
    .AddItem "PCS"
    .AddItem "Kotak"
    .AddItem "Kardus"
    .AddItem "Buah"
    .AddItem "Lusin"
    End With

    2. CODING ATUR FOLDER


    Dim ErwinG As String
    Private Sub CMDFOLDER_Click()
    On Error Resume Next
    MkDir (ThisWorkbook.Path & "\" & "FolderGambar")
    Call MsgBox("Folder penyimpanan gambar telah diatur dengan nama FolderGambar",
    vbInformation, "Data Gambar")

    3. CODING MEMASUKKAN GAMBAR


    On Error GoTo SALAH
    Dim Erwin As Integer
    Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
    Erwin = Application.FileDialog(msoFileDialogOpen).Show
    If Erwin <> 0 Then
    ErwinG = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
    Image1.Picture = LoadPicture(ErwinG)
    Image1.PictureSizeMode = 1
    Me.TXTGAMBAR.Value = ThisWorkbook.Path & "\" & "FolderGambar" & "\" &
    Me.TXTNAMABARANG.Value & ".jpg"
    End If
    Exit Sub
    SALAH:
    Call MsgBox("Tipe file tidak mendukung untuk ditampilkan, pastikan pilih file dengan tipe *.Jpg*,
    atau *.Jpeg*", vbInformation, "Simpan Gambar")

    4. CODING TAMBAH DATA


    On Error GoTo EXCELVBA
    Dim DbBarang As Object
    Set DbBarang = Sheet2.Range("B20000").End(xlUp)
    Dim GBARANG As String
    GBARANG = Me.TXTNAMABARANG.Value

    If Me.TXTKODE.Value = "" _
    Or Me.TXTNAMABARANG.Value = "" _
    Or Me.CMBKATEGORI.Value = "" _
    Or Me.TXTHARGABELI.Value = "" _
    Or Me.CMBSATUAN.Value = "" _
    Or Me.TXTISI.Value = "" Then
    Call MsgBox("Harap isi data barang dengan lengkap", vbInformation, "Data Barang")
    Else
    FileCopy ErwinG, ThisWorkbook.Path & "\" & "FolderGambar" & "\" & GBARANG & ".jpg"
    DbBarang.Offset(1, 0).Value = Me.TXTKODE.Value
    DbBarang.Offset(1, 1).Value = Me.TXTNAMABARANG.Value
    DbBarang.Offset(1, 2).Value = Me.CMBKATEGORI.Value
    DbBarang.Offset(1, 3).Value = Me.TXTHARGABELI.Value
    DbBarang.Offset(1, 4).Value = Me.CMBSATUAN.Value
    DbBarang.Offset(1, 5).Value = Me.TXTISI.Value

    DbBarang.Offset(1, 6).Value = Me.TXTHARGA1.Value


    DbBarang.Offset(1, 7).Value = Me.CMBSATUAN1.Value
    DbBarang.Offset(1, 8).Value = Me.TXTISI1.Value

    DbBarang.Offset(1, 9).Value = Me.TXTHARGA2.Value


    DbBarang.Offset(1, 10).Value = Me.CMBSATUAN2.Value
    DbBarang.Offset(1, 11).Value = Me.TXTISI2.Value

    DbBarang.Offset(1, 12).Value = Me.TXTHARGA3.Value


    DbBarang.Offset(1, 13).Value = Me.CMBSATUAN3.Value
    DbBarang.Offset(1, 14).Value = Me.TXTISI3.Value
    DbBarang.Offset(1, 15).Value = Me.TXTGAMBAR.Value

    Call MsgBox("Data barang berhasil ditambah", vbInformation, "Data Barang")


    On Error Resume Next
    Me.TABELBARANG.RowSource = Sheet2.Range("TBBARANG").Address(External:=True)

    Me.Image1.Picture = Nothing
    Me.TXTKODE.Value = ""
    Me.TXTNAMABARANG.Value = ""
    Me.CMBKATEGORI.Value = ""
    Me.TXTHARGABELI.Value = ""
    Me.CMBSATUAN.Value = ""
    Me.TXTISI.Value = ""

    Me.TXTHARGA1.Value = ""
    Me.CMBSATUAN1.Value = ""
    Me.TXTISI1.Value = ""

    Me.TXTHARGA2.Value = ""
    Me.CMBSATUAN2.Value = ""
    Me.TXTISI2.Value = ""
    Me.TXTHARGA3.Value = ""
    Me.CMBSATUAN3.Value = ""
    Me.TXTISI3.Value = ""
    Me.TXTGAMBAR.Value = ""
    End If
    Exit Sub
    EXCELVBA:
    Call MsgBox("Folder penyimpanan gambar belum diatur, silahkan tekan tombol folder untuk
    membuat folder penyimpanan gambar", vbInformation, "Simpan Gambar")

    5. CODING DOUBLE KLIK LISTBOX


    Application.ScreenUpdating = False
    On Error GoTo EXCELVBA
    Me.TXTKODE.Value = Me.TABELBARANG.Value
    Me.TXTNAMABARANG.Value = Me.TABELBARANG.Column(1)
    Me.CMBKATEGORI.Value = Me.TABELBARANG.Column(2)
    Me.TXTHARGABELI.Value = Me.TABELBARANG.Column(3)
    Me.CMBSATUAN.Value = Me.TABELBARANG.Column(4)
    Me.TXTISI.Value = Me.TABELBARANG.Column(5)

    Me.TXTHARGA1.Value = Me.TABELBARANG.Column(6)
    Me.CMBSATUAN1.Value = Me.TABELBARANG.Column(7)
    Me.TXTISI1.Value = Me.TABELBARANG.Column(8)

    Me.TXTHARGA2.Value = Me.TABELBARANG.Column(9)
    Me.CMBSATUAN2.Value = Me.TABELBARANG.Column(10)
    Me.TXTISI2.Value = Me.TABELBARANG.Column(11)

    Me.TXTHARGA3.Value = Me.TABELBARANG.Column(12)
    Me.CMBSATUAN3.Value = Me.TABELBARANG.Column(13)
    Me.TXTISI3.Value = Me.TABELBARANG.Column(14)
    Me.TXTGAMBAR.Value = Me.TABELBARANG.Column(15)
    Me.Image1.Picture = LoadPicture(Me.TXTGAMBAR.Value)
    Me.Image1.PictureSizeMode = 1
    Me.CMDTAMBAH.Enabled = False
    Sheet2.Select
    SUMBERUBAH = Sheets("DATABARANG").Cells(Rows.Count, "B").End(xlUp).Row
    Sheets("DATABARANG").Range("B6:B" & SUMBERUBAH).Find(What:=Me.TXTKODE.Value,
    LookIn:=xlValues, LookAt:=xlWhole).Activate
    CELLAKTIF = ActiveCell.Row
    Sheets("DATABARANG").Range("B" & CELLAKTIF & ":Q" & CELLAKTIF).Select
    Sheet1.Select
    Exit Sub
    EXCELVBA:
    Call MsgBox("Klik 2x pada data yang tersedia", vbInformation, "Pilih Data")

    6. CODING UPDATE DATA


    Application.ScreenUpdating = False
    Dim BARIS, SUMBERUBAH As String
    Dim GBARANG As String
    GBARANG = Me.TXTNAMABARANG.Value

    If Me.TXTKODE.Text = "" Then


    Call MsgBox("Pilih data terlebih dahulu", vbInformation, "Pilih Data")
    Else
    Sheet2.Select
    BARIS = ActiveCell.Row
    On Error Resume Next
    FileCopy ErwinG, ThisWorkbook.Path & "\" & "FolderGambar" & "\" & GBARANG & ".jpg"
    Cells(BARIS, 2) = Me.TXTKODE.Value
    Cells(BARIS, 3) = Me.TXTNAMABARANG.Value
    Cells(BARIS, 4) = Me.CMBKATEGORI.Value
    Cells(BARIS, 5) = Me.TXTHARGABELI.Value
    Cells(BARIS, 6) = Me.CMBSATUAN.Value
    Cells(BARIS, 7) = Me.TXTISI.Value

    Cells(BARIS, 8) = Me.TXTHARGA1.Value
    Cells(BARIS, 9) = Me.CMBSATUAN1.Value
    Cells(BARIS, 10) = Me.TXTISI1.Value

    Cells(BARIS, 11) = Me.TXTHARGA2.Value


    Cells(BARIS, 12) = Me.CMBSATUAN2.Value
    Cells(BARIS, 13) = Me.TXTISI2.Value

    Cells(BARIS, 14) = Me.TXTHARGA3.Value


    Cells(BARIS, 15) = Me.CMBSATUAN3.Value
    Cells(BARIS, 16) = Me.TXTISI3.Value

    Cells(BARIS, 17) = Me.TXTGAMBAR.Value

    On Error Resume Next


    Me.TABELBARANG.RowSource = Sheet2.Range("TBBARANG").Address(External:=True)
    Call MsgBox("Data berhasil di update", vbInformation, "Update Data")

    Me.TXTKODE.Value = ""
    Me.TXTNAMABARANG.Value = ""
    Me.CMBKATEGORI.Value = ""
    Me.TXTHARGABELI.Value = ""
    Me.CMBSATUAN.Value = ""
    Me.TXTISI.Value = ""

    Me.TXTHARGA1.Value = ""
    Me.CMBSATUAN1.Value = ""
    Me.TXTISI1.Value = ""

    Me.TXTHARGA2.Value = ""
    Me.CMBSATUAN2.Value = ""
    Me.TXTISI2.Value = ""

    Me.TXTHARGA3.Value = ""
    Me.CMBSATUAN3.Value = ""
    Me.TXTISI3.Value = ""
    Me.TXTGAMBAR.Value = ""
    Me.Image1.Picture = Nothing
    End If
    Sheet1.Select

    7. CODING DELETE DATA


    If Me.TXTKODE.Value = "" Then
    Call MsgBox("Pilih data pada tabel data", vbInformation, "Hapus Data")
    Else
    'Membuat pesan konfirmasi hapus data
    Select Case MsgBox("Anda akan menghapus data" _
    & vbCrLf & "Apakah anda yakin?" _
    , vbYesNo Or vbQuestion Or vbDefaultButton1, "Hapus data")
    Case vbNo
    Exit Sub
    Case vbYes
    End Select
    'Menentukan tempat hapus data, menghapus data dan membersihkan form
    Set Hapusdata = Sheet2.Range("B6:B500000").Find(What:=Me.TXTKODE.Value,
    LookIn:=xlValues)
    Hapusdata.Offset(0, 0).ClearContents
    Hapusdata.Offset(0, 1).ClearContents
    Hapusdata.Offset(0, 2).ClearContents
    Hapusdata.Offset(0, 3).ClearContents
    Hapusdata.Offset(0, 4).ClearContents
    Hapusdata.Offset(0, 5).ClearContents
    Hapusdata.Offset(0, 6).ClearContents
    Hapusdata.Offset(0, 7).ClearContents
    Hapusdata.Offset(0, 8).ClearContents
    Hapusdata.Offset(0, 9).ClearContents
    Hapusdata.Offset(0, 10).ClearContents
    Hapusdata.Offset(0, 11).ClearContents
    Hapusdata.Offset(0, 12).ClearContents
    Hapusdata.Offset(0, 13).ClearContents
    Hapusdata.Offset(0, 14).ClearContents
    Hapusdata.Offset(0, 15).ClearContents

    Call MsgBox("Data berhasil dihapus", vbInformation, "Hapus Data")

    Me.TXTKODE.Value = ""
    Me.TXTNAMABARANG.Value = ""
    Me.CMBKATEGORI.Value = ""
    Me.TXTHARGABELI.Value = ""
    Me.CMBSATUAN.Value = ""
    Me.TXTISI.Value = ""

    Me.TXTHARGA1.Value = ""
    Me.CMBSATUAN1.Value = ""
    Me.TXTISI1.Value = ""

    Me.TXTHARGA2.Value = ""
    Me.CMBSATUAN2.Value = ""
    Me.TXTISI2.Value = ""

    Me.TXTHARGA3.Value = ""
    Me.CMBSATUAN3.Value = ""
    Me.TXTISI3.Value = ""
    Me.TXTGAMBAR.Value = ""

    Call UrutBarang
    End If

    8. MODUL URUT DATA


    Sub UrutBarang()
    Application.ScreenUpdating = False
    Sheet2.Select
    Sheet2.Range("B6:Q200000").Sort KEY1:=Range("B6"), Order1:=xlAscending, Header:=xlYes
    End Sub

    9. CODING RESET
    Me.TXTKODE.Value = ""
    Me.TXTNAMABARANG.Value = ""
    Me.CMBKATEGORI.Value = ""
    Me.TXTHARGABELI.Value = ""
    Me.CMBSATUAN.Value = ""
    Me.TXTISI.Value = ""

    Me.TXTHARGA1.Value = ""
    Me.CMBSATUAN1.Value = ""
    Me.TXTISI1.Value = ""

    Me.TXTHARGA2.Value = ""
    Me.CMBSATUAN2.Value = ""
    Me.TXTISI2.Value = ""

    Me.TXTHARGA3.Value = ""
    Me.CMBSATUAN3.Value = ""
    Me.TXTISI3.Value = ""
    Me.TXTGAMBAR.Value = ""
    Me.CMDTAMBAH.Enabled = True

    10. CODING CARI DATA


    On Error GoTo SALAH
    Set Cari_Data = Sheet2
    Cari_Data.Range("S6").Value = "*" & Me.TXTCARI.Value & "*"
    Cari_Data.Range("B5").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
    Sheet2.Range("S5:S6"), CopyToRange:=Sheet2.Range("U5:AJ5"), Unique:=False
    Me.TABELBARANG.RowSource = Sheet2.Range("HASILCARI").Address(External:=True)
    Exit Sub
    SALAH:
    Call MsgBox("Maaf, data tidak ditemukan", vbInformation, "Cari Data")

    11. CODING RESET CARI


    On Error Resume Next
    Me.TABELBARANG.RowSource = Sheet2.Range("TBBARANG").Address(External:=True)
    Me.TXTCARI.Value = ""

    12. CODING TEXTBOX ISI


    On Error Resume Next
    If Me.TXTHARGABELI.Value = "" Then
    Me.TXTHARGASATUAN.Value = ""
    Else
    Me.TXTHARGASATUAN.Value =
    Application.WorksheetFunction.RoundUp(IIf(Me.TXTHARGABELI.Value = "", 0,
    Me.TXTHARGABELI.Value) / IIf(Me.TXTISI.Value = "", 0, Me.TXTISI.Value), -2)
    End If

    You might also like