Replace Text with Picture in Cell

[tlc1980]

Hello. I would really appreciate any help I can get. A link to my spreadsheet is below. On the Labels tab, I want to replace text (aaa, bbb, ccc, ddd, eee) with the corresponding picture which is housed on the Pictures tab. The picture would need to be resized since the labels must stay the same size since they are printed on Avery labels. Is it possible to have the picture centered where the text is? Thank you in advance!


https://www.dropbox.com/s/ijafmng4jyjuaq6/WellnessLabels.xlsm?dl=0

 

[tlc1980]

I cross posted. Thanks!

Replace Text with Picture in Cell

 

[Perpa]

Hello. I would really appreciate any help I can get. A link to my spreadsheet is below. On the Labels tab, I want to replace text (aaa, bbb, ccc, ddd, eee) with the corresponding picture which is housed on the Pictures tab. The picture would need to be resized since the labels must stay the same size since they are printed on Avery labels. Is it possible to have the picture centered where the text is? Thank you in advance!


https://www.dropbox.com/s/ijafmng4jyjuaq6/WellnessLabels.xlsm?dl=0

tlc1980,
I did a little digging and after several trials came up with the attached code. Sorry, I didn't look at your spreadsheet.
All my names were in row 5, every other column from column B to Column J. You can adjust the locations of your
picture names to fit your lables. To adjust the picture sizes to fit the lable you need to do the following:
On the 'Pictures' sheet - right click a picture, select 'Size and Properties'. You can adjust the height and width to fit
your lable. Repeat for the other pictures. I am using Excel 2007 and Windows 7.
Happy labelling!
Perpa

Sub PicturePasteTest()
    Dim lastRow As Integer, i As Integer
    Dim sPicName As String

    With Sheets("Lables")
        .Activate
        For i = 2 To 10 Step 2                 'This assumes the picture names are in columns B, D, F, H, and J
            sPicName = .Cells(5, i).Value   'In this example the names are all shown in row 5
            If sPicName <> "" Then
                Sheets("Pictures").Shapes(sPicName).Copy
                .Cells(5, i).Select
                .Paste
            End If
        Next
    End With
 End Sub

 

[tlc1980]

Thank you so much!

 

[tlc1980]

Hi Perpa,

Do you think your code would work if I wanted to search a string in a text instead of having the entire cell value equal the picture name? Each cell has something similar to the example below. I would like the code to search for "aaa" and paste the picture.

Macaroni and Cheese
Calories: 123 Fat: 123 g
Protein: 123 g Sodium: 123 mg
aaa

 

[Perpa]

Hi Perpa,

Do you think your code would work if I wanted to search a string in a text instead of having the entire cell value equal the picture name? Each cell has something similar to the example below. I would like the code to search for "aaa" and paste the picture.

Macaroni and Cheese
Calories: 123 Fat: 123 g
Protein: 123 g Sodium: 123 mg
aaa

tlc1980,
Given the format you have shown:

Macaroni and Cheese
 Calories: 123 Fat: 123 g 
 Protein: 123 g Sodium: 123 mg 
 aaa

The following code will do what you need. It asks you to 'Select the Name of the Dish you want a picture of'.
After the name is selected (clicked on) the cell address will show, then press 'OK' and your picture is placed below
over the cell with the 'aaa'. I noticed the above format had a SPACE before and after the ' aaa ' so I added a
TRIM to take care of that.
Perpa

Sub PicturePasteTest2()
   
    Dim myDish As String
    Dim myPic As String
    Dim cell As Range
    
    On Error Resume Next
    myDish = Application.InputBox(prompt:="Select the Name of the Dish you want a picture of", Type:=8)
    
    For Each cell In Sheets("Lables").UsedRange
        x = cell.Address
        If cell.Value = myDish Then
            myPic = Trim(Range(x).Offset(3, 0).Value)
            Sheets("Pictures").Shapes(myPic).Copy
            Range(x).Offset(3, 0).PasteSpecial
        End If

    Next cell
End Sub