Insert Image in Excel column VBA

Question

I have a VBA code, which inserts image in Excel sheet in the right cell wherever I type image name. But I want it to work only in A:B Ranges: Type Name in cells of column A and it will insert image in the next cell - column B. Here is my code which I want to modify:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim c As Range
    Dim myPath As String

    myPath = "P:\"

    Application.ScreenUpdating = False

    For Each c In Target.Cells
        If Not Dir(myPath & "*" & c.Text & "*") = "" Then
            InsertPicture myPath & Dir(myPath & "*" & c.Text & "*"), c.offset(0, 1)
        End If
    Next c

    Application.ScreenUpdating = True
End Sub
'

Sub InsertPicture(thePath As String, theRange As Range)

    With ThisWorkbook.ActiveSheet.Pictures.Insert(thePath)

        With .ShapeRange
            .LockAspectRatio = msoTrue
            .Width = theRange.Width
            .Height = theRange.Height
        End With

        .Left = theRange.Left
        .Top = theRange.Top
        .Placement = 1
        .PrintObject = True

    End With
End Sub

Thank you in advance!

Matteo NNZ · Accepted Answer · 2017-07-20 21:12:16Z

1

You just have to make a check on the Target.Address (the Target being the range modified). I would also make a count of the Target to make sure it's only one Range:

If (Target.Count > 1) Or (Split(Target.Address,"$")(1) <> "A") Then Exit Sub

Explanation: the test above should be done in the beginning of the action macro Worksheet_Change and does the following:

Target.Count returns the number of cells in the Target (i.e. the number of cells that have changed). Since I guess you want the user to type only one name of the image at a time, you check for the Target.Count and if it's larger than 1 you Exit Sub
The second check is on the column letter. The variable Target.Address returns the address like this (for example): $A$15. You Split this string by the delimiter $, so the function Split(Target.Address,"$") will return an array like this: ("","A","15"). You get the second element of it (1) and you test it against the value "A". If it's not "A" (i.e. if the user modified a cell that is not in the column A), then you Exit Sub and you don't attach any image.

answered Jul 20, 2017 at 21:12

Matteo NNZ

12.8k13 gold badges62 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Monika Over a year ago

I made changes and it works now. Thank you very much for detailed explanation!

Monika Over a year ago

Hi all. The code works excellent, but now I have another issue, I couldn't solve( When I send the file to external users, they don't see the images. Is there any way to modify the code so as after inserting it doesn't keep the link, because I guess it because of the links, that people not connecting my server are not able to see the images in the file.

Collectives™ on Stack Overflow

Insert Image in Excel column VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related