Add picture to shape

[jeffreybrown]

This code is working fine; however, can't seem to find how to get the picture to come from inside the workbook instead of a location on the computer?

I embedded the pictures within the workbook and the names are "Picture 3" and "Picture 4".

Also, realize you shouldn't use Select/Selection but can't seem to get away from it here.

Any thoughts?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    Set Rng = Target.Parent.Range("A1")
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Rng) Is Nothing Then Exit Sub
    
    ActiveSheet.Shapes("Rec").Select

    Select Case Target.Value

    Case "Pic1"

        Selection.ShapeRange.Fill.UserPicture "C:\Users\Jeff\Desktop\Pic1.png"

    Case "Pic2"

        Selection.ShapeRange.Fill.UserPicture "C:\Users\Jeff\Desktop\Pic2.png"

    End Select
    
    Application.Goto [A1]
End Sub

 

[MickG]

If you drag all the your pictures from the sheet to within the shape, then "Group" all the Pictures and the shape together, then by running this code (modified to suit) you can hide all the Pictures except the one you want to show.

[COLOR="Navy"]Sub[/COLOR] MG07May07
Private [COLOR="Navy"]Sub[/COLOR] CommandButton2_Click()
[COLOR="Navy"]Dim[/COLOR] oShape [COLOR="Navy"]As[/COLOR] Shape, s [COLOR="Navy"]As[/COLOR] Shape
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] oShape [COLOR="Navy"]In[/COLOR] ActiveSheet.Shapes
    [COLOR="Navy"]If[/COLOR] oShape.Type = msoGroup [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] s [COLOR="Navy"]In[/COLOR] oShape.GroupItems
          [COLOR="Navy"]If[/COLOR] Left(s.Name, 7) = "Picture" [COLOR="Navy"]Then[/COLOR] s.Visible = False
          [COLOR="Navy"]If[/COLOR] s.Name = "Picture 3" [COLOR="Navy"]Then[/COLOR] s.Visible = True
        [COLOR="Navy"]Next[/COLOR] s
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] oShape
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jeffreybrown]

Thanks Mick I'll give it a go

 

[MickG]

Here's a shorter verson , you may be able to use.!!!

[COLOR="Navy"]Sub[/COLOR] MG07May14
[COLOR="Navy"]Dim[/COLOR] shp [COLOR="Navy"]As[/COLOR] Shape
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] shp [COLOR="Navy"]In[/COLOR] ActiveSheet.Shapes("Group 15").GroupItems
MsgBox shp.Name
[COLOR="Navy"]Next[/COLOR] shp
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jeffreybrown]

Thanks Mick,

I was able to use a combination of your two codes and it works great.