Dim StatusAksi As String
Dim BarisIsian As LongPrivate Sub UserForm_Initialize()' Susun Tab Stop
Call IsiTabIndex'Kosongkan semua TextBox
Call KosongkanText(True)'Tak Ada Aksi
StatusAksi = ""'CommandButton Default
Me.cmdAdd.Caption = "Add/EDit"
Me.cmdClose.Caption = "Close"
End SubPrivate Sub cmdAdd_Click()'Kembalikan ke CommandButton Default
Me.cmdAdd.Caption = "Add/EDit"
Me.cmdClose.Caption = "Close"' Agar dapat diisi
Me.txtnam.Locked = False
' posisi baru yg akan diisi
Dim iRow As Long
Dim ws As Worksheet Set ws = Worksheets("Data Induk")
Select Case StatusAksi
Case "ADD"
'find first empty row in database
iRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row
Case "EDIT"
iRow = BarisIsian
End Select
'check for a part number
If Trim(Me.txtnam.Value) = "" Then
Me.txtnam.SetFocus
MsgBox "Masukkan Datanya Dulu"
Exit Sub
End If 'copy the data to the database
ws.Cells(iRow, 1).Value = iRow - 2
ws.Cells(iRow, 2).Value = Me.txtnam.Value
ws.Cells(iRow, 3).Value = Me.txtklmn.Value
ws.Cells(iRow, 4).Value = Me.txtkotlhr.Value
ws.Cells(iRow, 5).Value = Me.txttgllhr.Value
ws.Cells(iRow, 6).Value = Me.txtstat.Value
ws.Cells(iRow, 7).Value = Me.Txtalmt.Value
ws.Cells(iRow, 8).Value = Me.Txtkot.Value
ws.Cells(iRow, 9).Value = Me.txtagam.Value
ws.Cells(iRow, 10).Value = Me.txttglmsk.Value
ws.Cells(iRow, 11).Value = Me.txtunitmsk.Value
ws.Cells(iRow, 12).Value = Me.txtnid.Value
ws.Cells(iRow, 13).Value = Me.txtprk.Value
ws.Cells(iRow, 14).Value = Me.txtjab.Value
ws.Cells(iRow, 15).Value = Me.txtnpwp.Value
ws.Cells(iRow, 16).Value = Me.Txtisteri.Value
ws.Cells(iRow, 17).Value = Me.Txtank1.Value
ws.Cells(iRow, 18).Value = Me.Txtank2.Value
ws.Cells(iRow, 19).Value = Me.Txtank3.Value StatusAksi = ""
' Kosongkan
Call KosongkanText(True)' posisi fokus ke CmdAdd
Me.cmdAdd.SetFocus
End SubPrivate Sub cmdClose_Click()
If StatusAksi = "" Then' sbg Keluar
Unload Me
Else' Agar bisa di isi Me.txtnam.Locked = False' Aksi Add atau Edit
jadi sbg cancel StatusAksi = ""
Call KosongkanText(True)
Me.cmdAdd.Caption = "Add/EDit"
Me.cmdClose.Caption = "Close"
End If
End Sub
Private Sub txtnam_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift
As Integer)' jika kosong ke CmdAdd
If (KeyCode = 9 Or KeyCode = 13) And Me.txtnam.Text = "" Then
Me.cmdAdd.SetFocus
End SubPrivate Sub txtnam_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.txtnam.Text = "" Then' jika kosong keluar
Cancel = False
Exit Sub
End If
Dim ketemu As Range'cari dahulu pada 'data induk' di kolom B
Set ketemu = Worksheets("Data Induk").Range("B:B").Find(what:=txtnam.Value,
LookIn:=xlValues, LookAt:=XlLookAt.xlWhole)
If Not ketemu Is Nothing Then' Ada
BarisIsian = ketemu.Row
StatusAksi = "EDIT"
Me.txtklmn.Value = ketemu.Offset(0, 1).Value
Me.txtkotlhr.Value = ketemu.Offset(0, 2).Value
Me.txttgllhr.Value = ketemu.Offset(0, 3).Value
Me.txtstat.Value = ketemu.Offset(0, 4).Value
Me.Txtalmt.Value = ketemu.Offset(0, 5).Value
Me.Txtkot.Value = ketemu.Offset(0, 6).Value
Me.txtagam.Value = ketemu.Offset(0, 7).Value
Me.txttglmsk.Value = ketemu.Offset(0, 8).Value
Me.txtunitmsk.Value = ketemu.Offset(0, 9).Value
Me.txtnid.Value = ketemu.Offset(0, 10).Value
Me.txtprk.Value = ketemu.Offset(0, 11).Value
Me.txtjab.Value = ketemu.Offset(0, 12).Value
Me.txtnpwp.Value = ketemu.Offset(0, 13).Value
Me.Txtisteri.Value = ketemu.Offset(0, 14).Value
Me.Txtank1.Value = ketemu.Offset(0, 15).Value
Me.Txtank2.Value = ketemu.Offset(0, 16).Value
Me.Txtank3.Value = ketemu.Offset(0, 17).Value
Else' belum ada
StatusAksi = "ADD"
Call KosongkanText(False)
End If
' jika Add atau Edit maka akan muncul Simpan dan Cancel Me.cmdAdd.Caption
= "Simpan"
Me.cmdClose.Caption = "Cancel"' tak dapat edit isian nama
Me.txtnam.Locked = TrueEnd Sub
Sub KosongkanText(ByVal semua As Boolean)
'clear the data
If semua Then
Me.txtnam.Value = ""
End If
Me.txtklmn.Value = ""
Me.txtkotlhr.Value = ""
Me.txttgllhr.Value = ""
Me.txtstat.Value = ""
Me.Txtalmt.Value = ""
Me.Txtkot.Value = ""
Me.txtagam.Value = ""
Me.txttglmsk.Value = ""
Me.txtunitmsk.Value = ""
Me.txtnid.Value = ""
Me.txtprk.Value = ""
Me.txtjab.Value = ""
Me.txtnpwp.Value = ""
Me.Txtisteri.Value = ""
Me.Txtank1.Value = ""
Me.Txtank2.Value = ""
Me.Txtank3.Value = ""End SubSub IsiTabIndex() Me.txtnam.TabIndex = 1
Me.txtklmn.TabIndex = 2
Me.txtkotlhr.TabIndex = 3
Me.txttgllhr.TabIndex = 4
Me.txtstat.TabIndex = 5
Me.Txtalmt.TabIndex = 6
Me.Txtkot.TabIndex = 7
Me.txtagam.TabIndex = 8
Me.txttglmsk.TabIndex = 9
Me.txtunitmsk.TabIndex = 10
Me.txtnid.TabIndex = 11
Me.txtprk.TabIndex = 12
Me.txtjab.TabIndex = 13
Me.txtnpwp.TabIndex = 14
Me.Txtisteri.TabIndex = 15
Me.Txtank1.TabIndex = 16
Me.Txtank2.TabIndex = 17
Me.Txtank3.TabIndex = 18End Sub Semoga sesuaiTo:
[email protected]
From: [email protected]
Date: Thu, 1 Dec 2011 10:20:40 +0800
Subject: [belajar-excel] EDIT DATA
Salam kenal semuanya para pakar Excel
Saya
pendatang baru, dan langsung ingin minta bantuan dan bertanya masalah edit
data terhadap
data yang pernah kita entrykan karena ternyata ada kesalahan, seperti
contoh berikut :
Supaya jelas saya lampirkan pula contoh file nya.
Terima kasih kepada semua pakar pakar Excel.
Djati W
Surabaya
