MEMBUAT KONTROL DATA BASE DI EXCEL DENGAN VBA

'Tombol   Input

Private Sub CommandButton1_Click()

Dim iRow As Long

iRow = WorksheetFunction.CountA(Range("c:c")) + 5

Cells(iRow, 2).Value = TextBox2.Value

Cells(iRow, 3).Value = TextBox3.Value

Cells(iRow, 4).Value = TextBox4.Value

End Sub

'Tombol cari

Private Sub CommandButton2_Click()

'texbox interaktif menampilkan data baris pilihan pada sheet aktif

Cari = TextBox1.Value

With ActiveSheet.Range("a6:a50")

Set c = .Find(Cari, LookIn:=xlValues)

If Not c Is Nothing Then

Baris = c.row

'Menampilkan data "ISIAN "

'TextBox1.Value = ActiveSheet.Cells(Baris, 1).Value

TextBox2.Value = ActiveSheet.Cells(Baris, 2).Value

TextBox3.Value = ActiveSheet.Cells(Baris, 3).Value

TextBox4.Value = ActiveSheet.Cells(Baris, 4).Value

Else

MsgBox "Nama belum ada"

TextBox1 = vbNullString

Cancel = True

End If

End With

End Sub

'Tombol   edit

Private Sub CommandButton3_Click()

'perintah edit sesuai pilihan baris

Data = TextBox1.Value

With ActiveSheet.Range("A6:A58")

Set c = .Find(Data, LookIn:=xlValues)

If TextBox1.Value = "" Then

MsgBox "nama belum terdaftar", vbCritical

ElseIf Not c Is Nothing Then

Baris = c.row

ActiveSheet.Cells(Baris, 2).Value = TextBox2.Value

ActiveSheet.Cells(Baris, 3).Value = TextBox3.Value

ActiveSheet.Cells(Baris, 4).Value = TextBox4.Value

End If

End With

End Sub

'Tombol  Hapus

Private Sub CommandButton4_Click()

'mengosongkan data sheet

Data = TextBox1.Value

With ActiveSheet.Range("A6:A58")

Set c = .Find(Data, LookIn:=xlValues)

If TextBox1.Value = "" Then

MsgBox "nama belum terdaftar", vbCritical

ElseIf Not c Is Nothing Then

Baris = c.row

ActiveSheet.Cells(Baris, 3).Value = ""

End If

End With

Dim row As Long

LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).row

row = 6

For row = LastRow To row Step -1

If Cells(row, 3) = "" Then

Cells(row, 3).EntireRow.Delete

End If

Next row

End Sub

'Pastekan kode pada ListBox2

Private Sub ListBox1_Click()

TextBox1.Value = ListBox1.List(ListBox1.ListIndex, 0)

TextBox1.Value = Format(TextBox1.Value, "000")

End Sub

'Pastekan kode pada userform

Private Sub UserForm_Initialize()

ListBox1.ColumnCount = 4

ListBox1.ColumnWidths = "35;110;70;70"

ListBox1.RowSource = ("A6:d50")

TextBox1.Value = Format(TextBox1.Value, "000")

End Sub

Download Sampel file xlsm disini :

Userform Kontrol Data Base

SEMOGA BERMANFAAT !!