CODEWITHSUNDEEP
vbaexcel
Monika
Monika

Reputation: 61

Insert Image in Excel column VBA

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!

Upvotes: 0

Views: 346

Answers (1)

Matteo NNZ
Matteo NNZ

Reputation: 12645

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:

  1. 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
  2. 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.

Upvotes: 1

Related Questions