VBA help for a Macro (inserting picture from file)

juan_Guerra
Copper Contributor
Apr 10, 2024
dklein_14 ¿Cómo me vas a decir que te funcionó? Eso ¡no funciona!. Debe ser por la mala traducción al español.
¿Pueden bajar la macro sin traducirla? ¿Es posible?

Bronze Contributor

Mar 20, 2019

_{Hey dklein_14-}

_{Can you please tell me a few things for clarification?}

_{What are the 6 different ranges? If the ranges are on different worksheets can you further clarify with}_{worksheet names?}

_-Matt

dklein_14

Copper Contributor

Mar 21, 2019

Matt Mickle the locations for the 6 other images are as follows:

and all on same worksheet.
.Left = ActiveSheet.Range("q3").Left
.Top = ActiveSheet.Range("q3").Top
.Width = ActiveSheet.Range("q4:q8").Width
.Height = ActiveSheet.Range("r4:r8").Height
.Placement = 1

2
.Left = ActiveSheet.Range("q33").Left
.Top = ActiveSheet.Range("q33").Top
.Width = ActiveSheet.Range("q33:q38").Width
.Height = ActiveSheet.Range("r33:r38").Height

3
.Left = ActiveSheet.Range("q63").Left
.Top = ActiveSheet.Range("q63").Top
.Width = ActiveSheet.Range("q63:q68").Width
.Height = ActiveSheet.Range("r68:r68").Height

They are all about 30 cell spaces lower and I have 7 total.  In additional I have a sheet labeled Title Page and there 1 image there with the follow location.
Title Page
.Left = ActiveSheet.Range("B3").Left
.Top = ActiveSheet.Range("B3").Top
.Width = ActiveSheet.Range("B3:b9").Width
.Height = ActiveSheet.Range("e9:e9").Height

Thank you for your help!

Matt Mickle

Bronze Contributor

Mar 21, 2019

dklein_14

Try the below code example which loops through multiple locations on the worksheet. I have commented the code for better understanding.

You may want to note that when you set the width to a value like B3:B9 that is will simple make the image the length of the B column or similarly for E9:E9 it will make the height of the image the height of cell E9.

Sub GetPic()

Dim fNameAndPath     As Variant
Dim img              As Picture
Dim intLp            As Integer
Dim arrLeftandTop    As Variant
Dim arrWidth         As Variant
Dim arrHeight        As Variant

fNameAndPath = Application.GetOpenFilename(Title:="Select Picture To Be Imported") 'Open FIle Dialog to allow user to pick picture
If fNameAndPath = False Then Exit Sub 'If the user cancels then exit the procedure....

    'Define the different range arrays
    arrLeftandTop = Array("B3", "Q3", "Q33", "Q63") 'Left and top range values
    arrWidth = Array("B3:B9", "Q4:Q8", "Q33:Q38", "Q63:Q68") 'Width range values
    arrHeight = Array("E9:E9", "R4:R8", "R33:R38", "R68:R68") 'Height range values
    
    'Loop through ranges and place pictures one at a time....
    'Note arrays start at 0
    ' arrLeftandTop(0) = "B3"
    ' arrLeftandTop(1) = "Q3"
    '.....etc
    For intLp = 0 To 3
    
        Set img = ActiveSheet.Pictures.Insert(fNameAndPath) 'Set image for insert
        With img
           'Resize Picture to fit in the range....
           .Left = ActiveSheet.Range(arrLeftandTop(intLp)).Left
           .Top = ActiveSheet.Range(arrLeftandTop(intLp)).Top
           .Width = ActiveSheet.Range(arrWidth(intLp)).Width
           .Height = ActiveSheet.Range(arrHeight(intLp)).Height
           .Placement = 1
           .PrintObject = True
        End With
    
    Next intLp 'Go to next array value... i.e. first loop is 0, next loop is 1....etc.
    
End Sub

Forum Discussion

VBA help for a Macro (inserting picture from file)

Resources