Insert pictures to cells instead notes - Please correct VBA code RRS feed

  • Question

  • I want to correct this code (picture inserted as cell notes). Set the path to the pictures in the column "P" 
    I want to make the pictures inserted into the cells themselves (column "A"). 
    Can you help me?


    Sub InsertPicturesInComments()
        Dim rngPics As Range, rngOut As Range
        Dim i As Long, p As String, w As Long, h As Long
        Set rngPics = Range("P2:P235")    'диапазон путей к картинкам
        Set rngOut = Range("A2:A235")     'диапазон вывода примечаний
        rngOut.ClearComments        'удаляем старые примечания
        'проходим в цикле по ячейкам
        For i = 1 To rngPics.Cells.Count
            p = rngPics.Cells(i, 1).Value       'считываем путь к файлу картинки
            w = LoadPicture(p).Width            'и ее размеры
            h = LoadPicture(p).Height
            With rngOut.Cells(i, 1)
                .AddComment.Text Text:=""       'создаем примечание без текста
                .Comment.Visible = True
                .Comment.Shape.Select True
            End With
            With rngOut.Cells(i, 1).Comment.Shape   'заливаем картинкой
                .Fill.UserPicture p
                .ScaleWidth 1, msoFalse, msoScaleFromTopLeft
                .ScaleHeight h / w * 1.8, msoFalse, msoScaleFromTopLeft     'корректируем размеры
            End With
        Next i
    End Sub

    Thursday, April 3, 2014 7:47 AM

All replies

  • Re:  Inserting pictures

    You can use something similar to this to insert a picture (not a comment)...

     Dim strPicPath As String
     strPicPath = "C:\Documents and Settings\qwasiliy\My Documents\My Pictures\Girl in Pool.jpg"
     ActiveSheet.Shapes.AddPicture strPicPath, 0, 1, Range("D3").Left, Range("D3").Top, 350, 500
    Jim Cone
    Portland, Oregon USA (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Sunday, October 30, 2016 8:03 PM
    Thursday, April 3, 2014 12:06 PM
  • You can use this solution: Embed images in the cells

    Oskar Shon, Office System MVP -
    if Helpful; Answer when a problem solved

    Thursday, April 10, 2014 6:24 PM