BELAJAR USERFORM EDIT DATA DAN FOTO DI EXCEL

Buatlah sebuah Userform yang terdiri dari 2 CommandButton

satu untuk tombol input data dan satu lagi untuk input foto

yang mana tombol kedua akan disembunyikan namun akan dipanggil otomatis ketika tombol input di klik

Pastekan kode berikut pada CommandButton1

Private Sub CommandButton1_Click()

 If TextBox1.Value = "" Then

 MsgBox "Maaf Nama Foto Belum diisi", vbCritical

 Else

   On Error Resume Next

    Dim Filter As String, Title As String, FileIROW As String

    Dim SourceFile, DestinationFile

    irow.SetFocus

    Filter = "jpg Images File Only(*.jpg),*.jpg,"

    FileIROW = Application.GetOpenFilename(Filter, , Title)

    NamaFile = TextBox1.Value

    ActiveWorkbook.Image1.Picture = LoadPicture(FileIROW)

    Image1.Picture = LoadPicture(FileIROW)

    DestinationFile = ActiveWorkbook.Path & "\FOTO\" & NamaFile & ".jpg"

    FileCopy FileIROW, DestinationFile

    End If

    CommandButton3_Click

        End Sub

Pastekan kode berikut pada CommandButton2

Private Sub CommandButton2_Click()

 Data = ComboBox1.Value

With Worksheets("data").Range("A6:A50")

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

If Not c Is Nothing Then

Baris = c.Row

Worksheets("data").Cells(Baris, 1).Value = ComboBox1.Value

Worksheets("data").Cells(Baris, 2).Value = TextBox1.Value

Worksheets("data").Cells(Baris, 3).Value = TextBox2.Value

Worksheets("data").Cells(Baris, 4).Value = TextBox3.Value

Worksheets("data").Cells(Baris, 5).Value = TextBox4.Value

End If

End With

ComboBox1.SetFocus

    Image1.Picture = LoadPicture

    ComboBox1.Value = ""

     TextBox1.Value = ""

      TextBox2.Value = ""

       TextBox3.Value = ""

End Sub

Pastekan kode berikut pada Combobox1

Private Sub ComboBox1_Change()

ComboBox1.List = Sheets("Data").Range("A6:A100").Value

ListBox1.ColumnCount = 4

ListBox1.ColumnWidths = 35 & ";" & 100 & ";" & 100 & ";" & 80

ListBox1.RowSource = "data"

On Error Resume Next

    Dim Filter As String, Title As String, FileX As String

    X.SetFocus

    caridata = Me.ComboBox1.Value

    With Worksheets("DATA").Range("A6:A100")

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

    If Not c Is Nothing Then

    Baris = c.Row

    Me.TextBox1.Value = Worksheets("DATA").Cells(Baris, 2).Value

    Me.TextBox2.Value = Worksheets("DATA").Cells(Baris, 3).Value

    Me.TextBox3.Value = Worksheets("DATA").Cells(Baris, 4).Value

     Me.TextBox4.Value = Worksheets("DATA").Cells(Baris, 5).Value

    Else

    ComboBox1 = ""

    TextBox1 = ""

    TextBox2 = ""

    Image1.Picture = LoadPicture

    End If

    End With

    Application.ScreenUpdating = True

    DataFoto = TextBox1.Value

    Files = ActiveWorkbook.Path & "\FOTO\" & DataFoto & ".jpg"

    Image1.Picture = LoadPicture(Files)

    Application.ScreenUpdating = True

'With Sheets("data")

      '.OLEObjects("Image1").Object.Picture = Me.Image1.Picture

'End With

End Sub

Pastekan kode berikut pada Userform

Private Sub UserForm_Initialize()

ComboBox1.List = Sheets("Data").Range("A6:A100").Value

ListBox1.ColumnCount = 4

ListBox1.ColumnWidths = 35 & ";" & 100 & ";" & 100 & ";" & 80

ListBox1.RowSource = "data"

End Sub

Download sampel file  :   userform kontrol data dan foto