Help with VBA / Auto Insert Picture

aimkage

New Member
Joined
Mar 12, 2010
Messages
19
I need a master of VBA to code me a macro or UDF

Here is what I want :

I see too possibilities :

Option 1- a macro that automatically insert and resize a picture name with the value of the active cell ....

Ex:

Value of cell C30 = C:/path/image.jpg

I want to insert picture C:/path/image.jpg in that cell and resize it to fit the entire cell

Option 2 : a function

Ex :

If value of cell A1 = C:/path/image.jpg

I want to call in cell C30 a UDF like : =INSERTPIC(A30)
So it has to load C:/path/image.jpg and resize it too



Please Help Me

Thx
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi there, welcome to the board!

You can use this as a UDF as requested:
Code:
Function INSERTPIC(sFullName As String) As Variant

    Dim rng1 As Range, rCall As Range
    Dim oShp As Object, sFName As String
    Dim dblLeft As Double, dblRight As Double
    Dim dblHeight As Double, dblWidth As Double
    Dim dblTop As Double, dblBottom As Double
    
    If Dir(sFullName, vbNormal) = "" Then
        INSERTPIC = "Bad file path/name"
        Exit Function
    End If
    sFName = Right(sFullName, Len(sFullName) - InStrRev(sFullName, "\"))
    Set rCall = Application.Caller
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    For Each oShp In ActiveSheet.Shapes
        Set rng1 = Range(oShp.TopLeftCell, oShp.BottomRightCell)
        If Not Intersect(rng1, rCall) Is Nothing Then
            oShp.Delete
        End If
    Next
    
    Set oShp = rCall.Parent.Pictures.Insert(sFullName)
    dblTop = rCall.Top
    dblLeft = rCall.Left
    dblBottom = rCall.Offset(1, 0).Top
    dblRight = rCall.Offset(0, 1).Left
    dblWidth = dblRight - dblLeft
    dblHeight = dblBottom - dblTop
    
    oShp.Top = dblTop
    oShp.Left = dblLeft
    oShp.Width = dblWidth
    oShp.Height = dblHeight
    
    INSERTPIC = sFName
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
End Function
 
Upvote 0
Thx it works pretty well ... only two problems

1 : It seems that excel only load picture from the last folder I inserted a Picture ...

The actual directory of my picture is (I'm on Mac OS X) :

file:///Users/Marco/Documents/Inspection/2010-001/RP1.jpg

If A5 = file:///Users/Marco/Documents/Inspection/2010-001/RP1.jpg

=INSERTPIC(A5) will result in #value

If A5 = RP1.jpg

=INSERTPIC(A5) will works like a charm

Is there a way to respect the complete path and filename because I project to use this function to load picture from different folder ?


2 : Image will scale to the actual cell size height but is the image can be center in that cell because it loads at the top left corner ?


Thx
 
Upvote 0
1: I have no idea what you're talking about. Give exact details about what cells house what data, what cells house your formulas, and the behavior that's taking place. Trying to follow you is giving me a headache.

2. You want to stretch the picture? Right now it should keep the aspect ratio.
 
Upvote 0
Sorry

1:

If my photos are saved in the same folder as my workbook and I use =INSERTPIC(pic.jpg) ... it works

but if I put all my photos in a sub folder named Images and I use =INSERTPIC(/Images/pic.jpg) excel does not find the pic.jpg

2:

I like that photo ratios are conserved

But if my cell is 2"x2" but my photo is 1.5"x2" ... I would like this photo to be in the centre of the cell because now my photo load at the Top/Left corner. That leaves a 0.5" of blank in the right part of the cell.

Thx
 
Upvote 0
Add these two lines:
Code:
    oShp.Left = dblLeft + ((dblWidth - oShp.Width) / 2)
    oShp.Top = dblTop + ((dblHeight - oShp.Height) / 2)
Before the INSERTPIC = sFNameline near the bottom.
 
Upvote 0
Thx but I have a problem with the function :

When value changes and image has to change too, sometimes it inserts the picture on top of the one before and sometimes it delete the previous image and insert the new one.

If I have =INSERTPIC(A20) ....... and that A20 changes from image1.jpg to image2.jpg ..... I want image1.jpg to be deleted before image2.jpg loads

Thx
 
Last edited:
Upvote 0
It deletes it for me. Can you find any unusual set of circumstances where it doesn't delete the picture, so we can narrow it down a bit?
 
Upvote 0

Forum statistics

Threads
1,216,465
Messages
6,130,786
Members
449,591
Latest member
sharmavishnu413

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top