Auto update image VBA

martinus1988

New Member
Joined
Aug 13, 2015
Messages
15
Hi all,

It is probably simple, but could not figure it out.
The macro that i use is for getting product images for quotations.
The only problem is that i want it to autoupdate. (Now i have to click button to see images on sheet, before sending quote).
Code:
Sub AfbeeldingenOfferte()
picname1 = Range("E13") 'This is the picture name
picname2 = Range("E19") 'This is the picture name
picname3 = Range("E25") 'This is the picture name
picname4 = Range("E31") 'This is the picture name
picname5 = Range("E37") 'This is the picture name
picname6 = Range("E43") 'This is the picture name
picname7 = Range("E49") 'This is the picture name
picname8 = Range("E55") 'This is the picture name
picname9 = Range("E61") 'This is the picture name
picname10 = Range("E67") 'This is the picture name


    InsertPictureInRange "C:\Users\info\Desktop\Offertes\Product images\" & picname1 & ".png", _
        Range("B13:B17")
    InsertPictureInRange "C:\Users\info\Desktop\Offertes\Product images\" & picname2 & ".png", _
        Range("B19:B23")
    InsertPictureInRange "C:\Users\info\Desktop\Offertes\Product images\" & picname3 & ".png", _
        Range("B25:B29")
        InsertPictureInRange "C:\Users\info\Desktop\Offertes\Product images\" & picname4 & ".png", _
        Range("B31:B35")
        InsertPictureInRange "C:\Users\info\Desktop\Offertes\Product images\" & picname5 & ".png", _
        Range("B37:B41")
        InsertPictureInRange "C:\Users\info\Desktop\Offertes\Product images\" & picname6 & ".png", _
        Range("B43:B47")
        InsertPictureInRange "C:\Users\info\Desktop\Offertes\Product images\" & picname7 & ".png", _
        Range("B49:B53")
        InsertPictureInRange "C:\Users\info\Desktop\Offertes\Product images\" & picname8 & ".png", _
        Range("B55:B59")
        InsertPictureInRange "C:\Users\info\Desktop\Offertes\Product images\" & picname9 & ".png", _
        Range("B61:B65")
        InsertPictureInRange "C:\Users\info\Desktop\Offertes\Product images\" & picname10 & ".png", _
        Range("B67:B71")
        
End Sub


Sub InsertPictureInRange(PictureFileName As String, TargetCells As Range)
' inserts a picture and resizes it to fit the TargetCells range
Dim p As Object, t As Double, l As Double, w As Double, h As Double
    
    If Dir(PictureFileName) = "" Then Exit Sub
    ' import picture
    Set p = ActiveSheet.Pictures.Insert(PictureFileName)
    ' determine positions
    With TargetCells
        t = .Top + 1
        l = .Left + 1
        w = .Offset(0, .Columns.Count).Left - .Left
        h = .Offset(.Rows.Count, 0).Top - .Top
    End With
    ' position picture
    With p
        .Top = t
        .Left = l
        .Width = w
        .Height = h
    End With
    Set p = Nothing
End Sub
Thank you for helping.
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
I guess it depends on what event you want to trigger it. For one of these two methods or both, add in the ThisWorkbook object.

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  AfbeeldingenOfferte
End Sub

Private Sub Workbook_Open()
  AfbeeldingenOfferte
End Sub
I would recommend modifying AfbeeldingenOfferte using an array and a loop to make it easier to maintain and a bit more efficient.
 

Forum statistics

Threads
1,081,447
Messages
5,358,731
Members
400,509
Latest member
excellearner241

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top