RUMUS VBA UNTUK MENYISIPKAN GAMBAR SECARA OTOMATIS DI EXCEL

1.  Buka file yang akan di edit
2. klik developer, insert image (active X control)
3. klik properties, di kolom picture ganti dengan bitmap, close
4. klik view code
   
   
   
5. Rumus VBA yang berlaku :

Option Explicit

Private Sub Image1_BeforeDragOver(ByVal Cancel As MSForms.ReturnBoolean, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal DragState As MSForms.fmDragState, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim sFile As String, sPath As String

    If Target.Count = 1 Then

        If InStr(Target.Address & "|", "$L$5|") <> 0 Then

            ActiveSheet.Calculate

            'ganti rujukan path seperti contoh

            'drive:\folder\subfolder\

            'contoh : E:\myData\mySubData\

            sPath = ThisWorkbook.Path & "\GAMBAR KELULUSAN 2022\"

            sFile = Range("L5").Value & ".jpg"

            If LenB(Dir$(sPath & sFile)) <> 0 Then

                Image1.Picture = LoadPicture(sPath & sFile)

            Else

                Image1.Picture = LoadPicture(vbNullString)

            End If

        End If

    End If

End Sub