Scribd
Upload
31 views4 pages

Coding Form Data Surat

The document contains a VBA code for a user form that allows users to search for data in a worksheet named 'DataSurat' based on specified criteria. It includes functionalities for searching, opening hyperlinks to documents, and resetting the form. The code also manages user interactions and displays appropriate messages based on the search results.

Uploaded by

manu wasa
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
31 views4 pages

Coding Form Data Surat

The document contains a VBA code for a user form that allows users to search for data in a worksheet named 'DataSurat' based on specified criteria. It includes functionalities for searching, opening hyperlinks to documents, and resetting the form. The code also manages user interactions and displays appropriate messages based on the search results.

Uploaded by

manu wasa
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

CODING FORM DATA SURAT

Private Sub CMD_CARI_Click()

If Me.KATAKUNCI.Value = "" Then

MsgBox "Masukkan kriteria pencarian.", vbOKOnly + vbInformation, "Cari"

Exit Sub

End If

Application.ScreenUpdating = False

Dim sh As Worksheet

Dim sht As Worksheet

Set sh = ThisWorkbook.Sheets("DataSurat")

Set sht = ThisWorkbook.Sheets("CARISURAT")

Dim ish As Long

Dim isht As Long

Dim iColumn As Integer

ish = ThisWorkbook.Sheets("DataSurat").Range("A" & Application.Rows.Count).End(xlUp).Row

If Me.BERDASARKAN.Value = Empty Then

Call MsgBox("Silahkan masukkan kriteria pencarian", vbInformation, "Masukkan Kriteria")

Exit Sub

End If

iColumn = Application.WorksheetFunction.Match(Me.BERDASARKAN.Value, sh.Range("A5:G5"), 0)

If sh.FilterMode = True Then

sh.AutoFilterMode = False

End If

If Me.BERDASARKAN.Value = "Nomor KK" Then


sh.Range("A5:G" & ish).AutoFilter Field:=iColumn, Criteria1:=Me.KATAKUNCI.Value

Else

sh.Range("A5:G" & ish).AutoFilter Field:=iColumn, Criteria1:="*" & Me.KATAKUNCI.Value & "*"

End If

sht.Cells.Clear

sh.AutoFilter.Range.Copy sht.Range("A1")

Application.CutCopyMode = False

isht = sht.Range("A" & Application.Rows.Count).End(xlUp).Row

If isht > 1 Then

Me.TABELSURAT.RowSource = "CARISURAT!A2:G" & isht

Call MsgBox("Data surat berhasil ditemukan", vbInformation, "Cari Surat")

Else

Call MsgBox("Data surat tidak ditemukan", vbInformation, "Cari Surat")

End If

sh.AutoFilterMode = False

Application.ScreenUpdating = True

End Sub

Private Sub CMD_OPEN_Click()

On Error GoTo EXCELVBA

ThisWorkbook.FollowHyperlink (Me.TABELSURAT.Column(6))

Exit Sub

EXCELVBA:

Call MsgBox("Pilih data surat terlebih dahulu. Atau file surat tidak ditemukan", vbInformation, "File
Surat")

End Sub
Private Sub CMD_RESET_Click()

UserForm_Initialize

Me.BERDASARKAN.Value = ""

Me.KATAKUNCI.Value = ""

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()

Me.BackColor = RGB(42, 45, 50)

Dim iRow As Long

If Application.WorksheetFunction.CountA(Sheet4.Range("A6:A100000")) = 0 Then

Me.TABELSURAT.RowSource = ""

Else

iRow = Sheet4.Range("A" & Rows.Count).End(xlUp).Row

If iRow > 1 Then

Me.TABELSURAT.RowSource = "DataSurat!A6:G" & iRow

End If

End If

Me.BERDASARKAN.Value = ""

Me.KATAKUNCI.Value = ""

With BERDASARKAN

.AddItem "Nama Penduduk"

.AddItem "NIK"

.AddItem "KK"

.AddItem "SURAT"

End With
End Sub

You might also like