code vba excel

Private Sub CariSuratND_Click()

Nama = KOLOMCARI.Value

With Worksheets("DATABASE").Range("F4:F35")

Set F = .Find(KOLOMCARI, LookIn:=xlValues)

If Not F Is Nothing Then

Baris = F.Row

KOLOMCARI.Value = Worksheets("DATABASE").Cells(Baris, 6).Value

NO_URUT.Value = Worksheets("DATABASE").Cells(Baris, 1).Value

NO_AGENDA.Value = Worksheets("DATABASE").Cells(Baris, 2).Value

TGL_SURAT.Value = Worksheets("DATABASE").Cells(Baris, 3).Value

TGL_MASUK.Value = Worksheets("DATABASE").Cells(Baris, 4).Value

LAMPIRAN.Value = Worksheets("DATABASE").Cells(Baris, 5).Value

NO_SURAT.Value = Worksheets("DATABASE").Cells(Baris, 6).Value

SURAT_DARI.Value = Worksheets("DATABASE").Cells(Baris, 7).Value

PERIHAL.Value = Worksheets("DATABASE").Cells(Baris, 8).Value

DISPOSISI.Value = Worksheets("DATABASE").Cells(Baris, 9).Value

DISAMPAIKAN.Value = Worksheets("DATABASE").Cells(Baris, 10).Value

CATATAN.Value = Worksheets("DATABASE").Cells(Baris, 11).Value

Else

MsgBox "DATA TIDAK ADA DALAM DATABASE"

End If

End With

KOLOMCARI.SetFocus

End Sub

Private Sub Akademik_Click()

Dim khanghannadata1 As Long

DoEvents

Me.Link.Value = "http://akademik.unjani.ac.id/"

Me.WebBrowser3.Visible = False

End Sub

Private Sub Daloradius_Click()

Dim khanghannadata1 As Long

DoEvents

Me.Link.Value = "http://172.16.0.8/daloradius/login.php"

Me.WebBrowser3.Visible = True

End Sub

Private Sub FORM4_Click()

Me.WebBrowser3.Visible = True

End Sub

Private Sub FORMDISPOSISI_Click()

End Sub

Private Sub Hotspot_Click()

Dim khanghannadata1 As Long

DoEvents

Me.Link.Value = "http://hotspot.unjani.ac.id"

Me.WebBrowser3.Visible = True

End Sub

Private Sub NOURUT_Click()

Dim i As Long

A = Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))

Me.NO_URUT = A + "001"

Me.NO_URUT.SetFocus

End Sub

Private Sub Perihal_1_Click()

Nama = PERIHAL.Value

With Worksheets("Rekapsurat").Range("H2:H1000")

Set F = .Find(PERIHAL, LookIn:=xlValues)

If Not F Is Nothing Then

Baris = F.Row

PERIHAL.Value = Worksheets("Rekapsurat").Cells(Baris, 8).Value

NO_URUT.Value = Worksheets("Rekapsurat").Cells(Baris, 1).Value

NO_AGENDA.Value = Worksheets("Rekapsurat").Cells(Baris, 2).Value

TGL_SURAT.Value = Worksheets("Rekapsurat").Cells(Baris, 3).Value

TGL_MASUK.Value = Worksheets("Rekapsurat").Cells(Baris, 4).Value

LAMPIRAN.Value = Worksheets("Rekapsurat").Cells(Baris, 5).Value

NO_SURAT.Value = Worksheets("Rekapsurat").Cells(Baris, 6).Value

SURAT_DARI.Value = Worksheets("Rekapsurat").Cells(Baris, 7).Value

PERIHAL.Value = Worksheets("Rekapsurat").Cells(Baris, 8).Value

DISPOSISI.Value = Worksheets("Rekapsurat").Cells(Baris, 9).Value

DISAMPAIKAN.Value = Worksheets("Rekapsurat").Cells(Baris, 10).Value

CATATAN.Value = Worksheets("Rekapsurat").Cells(Baris, 11).Value

Else

MsgBox "DATA TIDAK ADA DALAM DATABASE"

End If

End With

PERIHAL.SetFocus

End Sub

Private Sub Preview_Click()

End Sub

Private Sub tampilkan2_Click()

Dim Low As Long

Dim High As Long

Dim Tabel_Data As Long

Low = 0

High = 0

TabelData = Int((Low - Low + 1) * Rnd() + Low)

DoEvents

Me.NO_AGENDA.Value = "00/B.Sisfo&Humas/ /202" & TabelData

End Sub

Private Sub tampilkan3_Click()

Dim tampilkan3 As Date

TGL_SURAT = WorksheetFunction.Text(Date, "[$-421]dddd, dd mmmm yyyy")

DoEvents

End Sub

Private Sub tampilkan4_Click()

Dim tampilkan3 As Date

TGL_MASUK = WorksheetFunction.Text(Date, "[$-421]dddd, dd mmmm yyyy")

DoEvents

End Sub

Private Sub tampilkan5_Click()

Dim Low As Long

Dim High As Long

Dim Tabel_Data As Long

Low = 0

High = 0

TabelData = Int((Low - Low + 1) * Rnd() + Low)

DoEvents

Me.NO_SURAT.Value = "00/Unjani/ /202" & TabelData

End Sub

Private Sub TRANSFER_Click()

End Sub

Private Sub WebBrowser2_StatusTextChange(ByVal Text As String)

End Sub

Private Sub Webunjani_1_Click()

Dim khanghannadata1 As Long

DoEvents

Me.Link_1.Value = "http://unjani.ac.id/home.php"

Me.WebBrowser2.Visible = True

End Sub

Private Sub Webunjani_Click()

Dim khanghannadata1 As Long

DoEvents

Me.Link.Value = "http://unjani.ac.id/home.php"

Me.WebBrowser3.Visible = True

End Sub

Private Sub NO_AGENDA_Change()

Sheet6.Range("nomoragenda").Value = Me.NO_AGENDA.Value

End Sub

Private Sub TGL_SURAT_Change()

Sheet6.Range("tanggalsurat").Value = Me.TGL_SURAT.Value

End Sub

Private Sub TGL_MASUK_Change()

Sheet6.Range("tanggalmasuk").Value = Me.TGL_MASUK.Value

End Sub

Private Sub LAMPIRAN_Change()

Sheet6.Range("lampiran").Value = Me.LAMPIRAN.Value

End Sub

Private Sub NO_SURAT_Change()

Sheet6.Range("nomorsurat").Value = Me.NO_SURAT.Value

End Sub

Private Sub SURAT_DARI_Change()

Sheet6.Range("suratdari").Value = Me.SURAT_DARI.Value

End Sub

Private Sub PERIHAL_Change()

Sheet6.Range("perihal").Value = Me.PERIHAL.Value

End Sub

Private Sub DISPOSISI_Change()

Sheet6.Range("isidisposisi").Value = Me.DISPOSISI.Value

End Sub

Private Sub DISAMPAIKAN_Change()

Sheet6.Range("disampaikankepada").Value = Me.DISAMPAIKAN.Value

End Sub

Private Sub CATATAN_Change()

Sheet6.Range("Catatan").Value = CATATAN.Value

End Sub

Private Sub rekap_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

On Error GoTo Salah

Me.NO_URUT.Value = Me.rekap.Value

Me.NO_AGENDA.Value = Me.rekap.Column(1)

Me.TGL_SURAT.Value = Me.rekap.Column(2)

Me.TGL_MASUK.Value = Me.rekap.Column(3)

Me.LAMPIRAN.Value = Me.rekap.Column(4)

Me.NO_SURAT.Value = Me.rekap.Column(5)

Me.SURAT_DARI.Value = Me.rekap.Column(6)

Me.PERIHAL.Value = Me.rekap.Column(7)

Me.DISPOSISI.Value = Me.rekap.Column(8)

Me.DISAMPAIKAN.Value = Me.rekap.Column(9)

Me.CATATAN.Value = Me.rekap.Column(10)

Me.NO_URUT.Enabled = False

Me.SIMPAN.Enabled = False

Exit Sub

Salah:

Call MsgBox("Silahkan pilih data pada tabel data", vbInformation, "Data Surat")

End Sub

Private Sub BERSIHKAN_Click()

Me.NO_URUT.Enabled = True

Me.SIMPAN.Enabled = True

Me.NO_URUT.Value = ""

Me.NO_AGENDA.Value = ""

Me.TGL_SURAT.Value = ""

Me.TGL_MASUK.Value = ""

Me.LAMPIRAN.Value = ""

Me.NO_SURAT.Value = ""

Me.SURAT_DARI.Value = ""

Me.PERIHAL.Value = ""

Me.DISPOSISI.Value = ""

Me.DISAMPAIKAN.Value = ""

Me.CATATAN.Value = ""

End Sub

Private Sub PRINTTING_Click()

If Me.NO_URUT.Value = "" _

Or Me.NO_AGENDA.Value = "" _

Or Me.TGL_SURAT.Value = "" _

Or Me.TGL_MASUK.Value = "" _

Or Me.LAMPIRAN.Value = "" _

Or Me.NO_SURAT.Value = "" _

Or Me.SURAT_DARI.Value = "" _

Or Me.PERIHAL.Value = "" _

Or Me.DISPOSISI.Value = "" _

Or Me.DISAMPAIKAN.Value = "" _

Or Me.CATATAN.Value = "" Then

Call MsgBox("Isi semua data surat terlebih dahulu", vbInformation, "Data Surat")

Else

Sheet6.PrintOut

End If

End Sub

Private Sub Hapus_Click()

If Me.NO_URUT.Value = "" Then

Call MsgBox("Silahkan pilih data yamg akan dihapus", vbInformation, "Hapus Data")

Exit Sub

End If

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

Set HAPUS_DATA = Sheet1.Range("A2:A20000").Find(What:=Me.NO_URUT.Value, LookIn:=xlValues)

Sheet1.Select

HAPUS_DATA.Offset(0, 0).Select

Range(Selection, Selection.End(xlToRight)).Select

Selection.Delete Shift:=xlUp

Me.NO_URUT.Value = ""

Me.NO_AGENDA.Value = ""

Me.TGL_SURAT.Value = ""

Me.TGL_MASUK.Value = ""

Me.LAMPIRAN.Value = ""

Me.NO_SURAT.Value = ""

Me.SURAT_DARI.Value = ""

Me.PERIHAL.Value = ""

Me.DISPOSISI.Value = ""

Me.DISAMPAIKAN.Value = ""

Me.CATATAN.Value = ""

End Sub

Private Sub RUBAH_Click()

On Error GoTo Salah

If Me.NO_URUT.Value = "" Then

Call MsgBox("Pilih data yang mau diubah", vbInformation, "Data Surat")

Else

Set UbahSurat = Sheet1.Range("A2:A2000").Find(What:=Me.NO_URUT.Value, LookIn:=xlValues)

UbahSurat.Offset(0, 0).Value = Me.NO_URUT.Value

UbahSurat.Offset(0, 1).Value = Me.NO_AGENDA.Value

UbahSurat.Offset(0, 2).Value = Me.TGL_SURAT.Value

UbahSurat.Offset(0, 3).Value = Me.TGL_MASUK.Value

UbahSurat.Offset(0, 4).Value = Me.LAMPIRAN.Value

UbahSurat.Offset(0, 5).Value = Me.NO_SURAT.Value

UbahSurat.Offset(0, 6).Value = Me.SURAT_DARI.Value

UbahSurat.Offset(0, 7).Value = Me.PERIHAL.Value

UbahSurat.Offset(0, 8).Value = Me.DISPOSISI.Value

UbahSurat.Offset(0, 9).Value = Me.DISAMPAIKAN.Value

UbahSurat.Offset(0, 10).Value = Me.CATATAN.Value

Call MsgBox("Data Surat telah diubah", vbInformation, "Data Surat")

Me.NO_URUT.Value = ""

Me.NO_AGENDA.Value = ""

Me.TGL_SURAT.Value = ""

Me.TGL_MASUK.Value = ""

Me.LAMPIRAN.Value = ""

Me.NO_SURAT.Value = ""

Me.SURAT_DARI.Value = ""

Me.PERIHAL.Value = ""

Me.DISPOSISI.Value = ""

Me.DISAMPAIKAN.Value = ""

Me.CATATAN.Value = ""

End If

Exit Sub

Salah:

Call MsgBox("Ubah surat selain pada nomor surat", vbInformation, "Data Surat")

End Sub

Private Sub SIMPAN_Click()

Dim DataSurat As Object

Dim i As Long

Me.Label112.TextAlign = fmTextAlignCenter

Set DataSurat = Sheet1.Range("A20000").End(xlUp)

If Me.NO_URUT.Value = "" _

Or Me.NO_AGENDA.Value = "" _

Or Me.TGL_SURAT.Value = "" _

Or Me.TGL_MASUK.Value = "" _

Or Me.LAMPIRAN.Value = "" _

Or Me.NO_SURAT.Value = "" _

Or Me.SURAT_DARI.Value = "" _

Or Me.PERIHAL.Value = "" _

Or Me.DISPOSISI.Value = "" _

Or Me.DISAMPAIKAN.Value = "" _

Or Me.CATATAN.Value = "" Then

Call MsgBox("Isi semua data surat terlebih dahulu", vbInformation, "Data Surat")

Else

DataSurat.Offset(1, 0).Value = Me.NO_URUT.Value

DataSurat.Offset(1, 1).Value = Me.NO_AGENDA.Value

DataSurat.Offset(1, 2).Value = Me.TGL_SURAT.Value

DataSurat.Offset(1, 3).Value = Me.TGL_MASUK.Value

DataSurat.Offset(1, 4).Value = Me.LAMPIRAN.Value

DataSurat.Offset(1, 5).Value = Me.NO_SURAT.Value

DataSurat.Offset(1, 6).Value = Me.SURAT_DARI.Value

DataSurat.Offset(1, 7).Value = Me.PERIHAL.Value

DataSurat.Offset(1, 8).Value = Me.DISPOSISI.Value

DataSurat.Offset(1, 9).Value = Me.DISAMPAIKAN.Value

DataSurat.Offset(1, 10).Value = Me.CATATAN.Value

Call MsgBox("Data Surat telah ditambah", vbInformation, "Data Surat")

Me.NO_URUT.Value = ""

Me.NO_AGENDA.Value = ""

Me.TGL_SURAT.Value = ""

Me.TGL_MASUK.Value = ""

Me.LAMPIRAN.Value = ""

Me.NO_SURAT.Value = ""

Me.SURAT_DARI.Value = ""

Me.PERIHAL.Value = ""

Me.DISPOSISI.Value = ""

Me.DISAMPAIKAN.Value = ""

Me.CATATAN.Value = ""

End If

End Sub

Private Sub CARIFILESURAT_Click()

Nama = KOLOMCARI.Value

With Worksheets("Rekapsurat").Range("F2:F1000")

Set F = .Find(KOLOMCARI, LookIn:=xlValues)

If Not F Is Nothing Then

Baris = F.Row

KOLOMCARI.Value = Worksheets("Rekapsurat").Cells(Baris, 6).Value