VBA help for importing images from file

Question

I just started to use VBA and would really appreciate if someone could help me out. I would like to view images according to a path stored in a database. The images should refresh if a value in a specific cell is changed. I already figured out how to import one image according to the database, which is connected to that specific cell through INDEX and MATCH formulas. Unfortunately my knowledge is not enough to write a code which allows to import several images at the same time. Also it seems like sometimes previous images wont get removed and a new image is getting imported on top of the old one. If there is no image at all or no matching path in the database, a run-time error is exposed.

Is it even possible to find a clean solution for my problem and import several images at the same time? I would be really thankful if somebody could lead me in the right direction and at least help me deal with the runtime errors. Huge thanks in advance!

Database: Database

Display + specific Cell (in red): Display

Code used to import the first image:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$CG$1" Then
    Shapes(1).Delete
    Pictures.Insert(ActiveSheet.Range("V21").Value).Select
    With Selection
        .Height = ActiveSheet.Range("V21:CI40").Height
        .Width = ActiveSheet.Range("V21:CI40").Width
        .Left = ActiveSheet.Range("V21").Left
        .Top = ActiveSheet.Range("V21").Top
    End With
    Range("C3").Select
End If
End Sub

Full Problem with images and excel file in a ZIP:

https://techcommunity.microsoft.com/t5/excel/vba-help-for-importing-images-from-file/m-p/2181206

Answer 1

Try this:

Option Explicit

Private Sub ScrollBar1_Change()
    Range("$CG$1").Value = Range("$CG$1").Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    
    'Checking the Target address.
    If Target.Address = "$CG$1" Then
        
        'Declarations.
        Dim ObjImageTop As Object
        Dim ObjImageBottom As Object
        Dim RngImageTop As Range
        Dim RngImageBottom As Range
        Dim StrImageTopName As String
        Dim StrImageBottomName As String
        Dim StrImageTopAddress As String
        Dim StrImageBottomAddress As String
        
        'Settings.
        StrImageTopName = "Img_Top"
        StrImageBottomName = "Img_Bottom"
        StrImageTopAddress = Sheets("Monitor").Range("V21").Value
        StrImageBottomAddress = Sheets("Monitor").Range("V42").Value
        Set RngImageTop = Sheets("Monitor").Range("V21:CI40")
        Set RngImageBottom = Sheets("Monitor").Range("V42:CI73")
        
        'Deleting the previous images.
        On Error Resume Next
        Sheets("Monitor").Shapes(StrImageTopName).Delete
        Sheets("Monitor").Shapes(StrImageBottomName).Delete
        Err = 0
        On Error GoTo 0
        
        'Inserting the new ObjImageTop.
        Set ObjImageTop = Sheets("Monitor").Pictures.Insert(StrImageTopAddress)
        With ObjImageTop
            .Name = StrImageTopName
            .Height = RngImageTop.Height
            .Width = RngImageTop.Width
            .Left = RngImageTop.Left
            .Top = RngImageTop.Top
        End With
        
        'Inserting the new ObjImageBottom.
        Set ObjImageTop = Sheets("Monitor").Pictures.Insert(Sheets("Monitor").Range("V42").Value)
        With ObjImageTop
            .Name = StrImageBottomName
            .Height = RngImageBottom.Height
            .Width = RngImageBottom.Width
            .Left = RngImageBottom.Left
            .Top = RngImageBottom.Top
        End With
    
    End If
    
End Sub

The Private Sub ScrollBar1_Change() makes sure that if you change the number via the scrollbar, it will activate the Worksheet_Change event. Option Explicit is optional (more information about it here). In the 'Setting part you can customize it. You can change the names that will be given to the images, their address and the ranges they will be placed on. You could also not rely on those cells (V21 and V42) and instead determine your addresses in the code itself.